DAX code to track project progress as at reporting date

[masterelaichi]

Hi all,

First up, I have posted this query on another forum https://powerpivotforum.com.au/viewtopic.php?f=6&t=1305

I am working on a dashboard that can track all the projects that are currently being undertaken. The aim is to report on the progress of the various projects over a period of time. The data is being sourced from monthly excel files that are filled in manually (which is a major pain point as it is!). Over time, projects status change. For example, if in the August 2018 report a project is closed, it is not reported in the September 2018. Kind of like an inventory of items

I'll try and explain the dataset if it helps. There are numerous columns that contain various project parameters such Project Status, which is RED, AMBER, and GREEN; Project Stages, delivery, initiaite, close, etc; Project Assurance Levels, amongst others. The data structure is further complicated by additional four funding columns- Financial Year1, Year2, Year3, Year4 funding. Due to this structure, I have to unpivot the funding year columns because of which there are 4 additional rows for each and every single project. Multiplying that with the different reporting cycles just makes the report extremely vast, with repeated information for other static columns

I have managed to figure out how to obtain all the projects being reported in a specific reporting period using the following code (if there is a better way to do it, I would appreciate that)

Total number of Initiatives =
CALCULATE (
DISTINCTCOUNT ( Project[Project ID] ),
FILTER ( 'Calendar', 'Calendar'[Date] = MAX ( Project[Report As At] ) )


The formula I have used to calculate the actual cost to date is

Cost to date Using SUMMARIZE and SUMX =

CALCULATE(
SUMX(
SUMMARIZE(Project,Project[Project ID],Project[Cost to date]),Project[Cost to date]),
FILTER( Project,Project[Report As At]=MAX(Project[Report As At])
))

Again, if there is a better way of doing it, do let me know. There are other expenditure columns such a original expenditure, revised expenditure, etc, that are not shown here. The actual cost to date is what changes every month as the project progresses

Looking at the image below, I am able to get the correct number of projects being reported in a reporting period, 3 in this case as there are 3 projects being reported in the September report. But, the doughnut chart shows 4 projects. According to the table visual, the red project is Project Name 3, which was reported in 31/08/2018

<dl class="file"><dt class="attach-image">

</dt><dd>Summary-all projects</dd><dd>1.Project Dashboard Test.JPG (115.17 KiB) Viewed 32 times</dd></dl>



But in the latest reports, it progressed to Amber. Hopefully, the image below will provide more clarification. Selecting Project 3 should have changed to visual to show only AMBER as the status and Initiate as the project stage


<dl class="file"><dt class="attach-image">

</dt><dd>Project 3 filtered- showing different categories in visual</dd><dd>2.Project Dashboard Test.JPG (93.85 KiB) Viewed 32 times</dd></dl>



How do I go about showing the correct number for a reporting period. Do note that there are other columns in the actual dataset that are usually static but can possibly change as well. But the ones that change the most frequently are Project Status, Stage,Project Explanatory notes (not shown in this example)

Report As At	Project ID	Project Name	Project Stage	Finances as At	Cost to date	Status	Funding Year1	Funding Year 2	Funding Year 3	Funding Year 4
30/09/2018	Project-ID1	Project Name 1	Delivery		100000	Green	10000	0	0	0
30/09/2018	Project-ID2	Project Name 2	Closed		150000	Green	25000	0	0	0
30/09/2018	Project-ID3	Project Name 3	Initiate		200000	Amber	30000	35000	0	0
31/08/2018	Project-ID1	Project Name 1	Delivery		75000	Amber	10000	0	0	0
31/08/2018	Project-ID2	Project Name 2	Initiate		120000	Green	25000	25000	0	0
31/08/2018	Project-ID3	Project Name 3	Paused		70000	Red	30000	0	0	0
31/08/2018	Project-ID4	Project Name 4	Closed		5000	Green	0	0		0

<tbody>
</tbody>

Hope, I have provided sufficient information. Any help will be appreciated. I was not able to upload attachments so I have pasted the excel dataset here

Thank you