DAX code to track project progress as at reporting date


New Member
Sep 29, 2014
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 =
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 =

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 AtProject IDProject NameProject StageFinances as AtCost to dateStatusFunding Year1Funding Year 2Funding Year 3Funding Year 4
30/09/2018Project-ID1Project Name 1Delivery100000Green10000000
30/09/2018Project-ID2Project Name 2Closed150000Green25000000
30/09/2018Project-ID3Project Name 3Initiate200000Amber300003500000
31/08/2018Project-ID1Project Name 1Delivery75000Amber10000000
31/08/2018Project-ID2Project Name 2Initiate120000Green250002500000
31/08/2018Project-ID3Project Name 3Paused70000Red30000000
31/08/2018Project-ID4Project Name 4Closed5000Green000


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

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Latest member