DAX code to track project progress as at reporting date

masterelaichi

New Member
Joined
Sep 29, 2014
Messages
45
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 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

<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
 

Forum statistics

Threads
1,082,342
Messages
5,364,777
Members
400,815
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top