Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Tracking project status and totals over time

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

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

1.Project Dashboard Test.JPGSummary-all projects 1.Project Dashboard Test.JPG (115.17 KiB) Viewed 65 times




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


2.Project Dashboard Test.JPGProject 3 filtered- showing different categories in visual 2.Project Dashboard Test.JPG (93.85 KiB) Viewed 65 times




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

 

I have added a sample dataset above as I couldn't upload the excel and PBI file

 

Any help will be truly appreciated

 

Thank you

12 REPLIES 12
v-lili6-msft
Community Support
Community Support

hi, @Anonymous

It seems that the data you provide is a bit confusing, could you please provide a simple sample pbix file and the expected output

You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

hi..was wondering if you had any suggestions for me and if you are able to access the files

 

Thanks

hi, @Anonymous

Sorry for late reply, There were a lot of emails in the mailbox that I didn't notice this.

I'm not able to access the files from your link, but I use the data in the picture do a test.

Here I explain your confusion:

1.JPG

First 

 

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

This formula calculate how many the projects being reported in a specific reporting period

 

The result is different between total and the sum of row result is involved the row context and filter context .

 

the result in the row of visual is calculated according to the current line and the total is calculated according to the whole table

for example:

Since you don't select any Report As At from slicer.

for each row in the lower left corner of table visual, Max( Project[Report As At ])="Calendar"[Date] , so Total number of Initiatives is 1 for each row, but for the total,  Max( Project[Report As At ]) is 2018-09-30, so It just calcuate the date is "2018-09-30" and the result is 3.

And the table visual in the top right corner, it is an aggregate summary table visual for the table visual in the lower left corner.

 

and if you select the doughnut chart and change it to table visual, you will find it is the same visual as the table visual in the top right corner.

 

Second

You may try to use this formula to add a measure

Measure = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)

then add it into the visual level filter and set filter result is "1" as  below:

BeforeBeforeAfterAfter

 

Here is my demo pbix, please try it.

https://www.dropbox.com/s/ugbc7nm21kmmud0/Tracking%20project%20status%20and%20totals%20over%20time.pbix?dl=0

 

All your confusion comes from the row context and filter context in DAX.

 

Sorry again for the late reply.

 

Best Regards,

Lin

 

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for your response, Lin

 

I realised that it was something to do with my filter context. Just wasn't sure how to resolve it. Could you please explain the logic behind this code -

 

Measure = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)

This requires that the above measure to be added to the report filter and select 1 for every report on the page, correct? I wasn't abe to add this measure at the Page or Report level. The status is just one of the pie chart. There are more such as the Project Stage

 

I was having a go at this myself and not sure if there is a better way of doing it

 

I created a calculated column ISLatest which says looks at the Report As At date and marks it as "Latest" or "Not Latest" (I think this is a redundant step and can be avoided)

 

Then, I created the below measure

 

Measure WIP = 

IF( 
    ISFILTERED(Project[Report As At]),[Total number of Initiatives],
CALCULATE(
   [Total number of Initiatives],
    FILTER('Calendar','Calendar'[Date]=MAX(Project[Report As At])), FILTER(Project,Project[ISlatest]="Latest"))
    
)

Then I ended up with this which is sort of what I wanted to achieve

 

 I am unable to paste an image for some reason. I just can't paste it like the way I did in my initial post

 

Anonymous
Not applicable

Posting the image of what I ended up with

image.png

 

 

 

Any suggestions? If there is a better way of doing it, I would really appreciate as this seems like a convoluted way of doing it

 

Thanks again!

 

hi, @Anonymous

First

I wasn't able to add this measure at the Page or Report level. It is only can be added into visual level filter, not Page or Report level

Second

Measure = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)

 CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))  returns the max Project [Report As At], It's going to have the same effect as calculated column ISLatest

And measure is dynamic and column is static.

for example:

If you only select Project Name 4, column still returns 2018-09-30 but my measure will return 2018-08-31

and it will lead to error result.

Your measure in the doughnut chart will return blank valueYour measure in the doughnut chart will return blank value

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for that. So, I need to add Measure and filter it to 1 for every visual in the report

 

 

hi, @Anonymous

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Lin,

 

Thanks for your help with this. I am having another issue summing up Cost to Date. The formula I have used is

 

Cost to date Using SUMMARIZE and SUMX = 

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

 

One of the measures I am trying to work out is the Cost to date of all the projects that have been closed so far. The

 

3.Closed Project .JPG

 

 

The result that I expect is the Cost to dates ofr all the projects that have been closed so far, which is $165,000. But instead, I am getting the cost to date of the Project which has the latest Report As At date. In this case Project 2,  $150,000

 

Truly appreciate any help

 

 

hi, @Anonymous

This is a measure totals problem. Very common.  See these two posts about it here:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi, @Anonymous

For your requirement, it needs you to add Measure and filter it to 1 for every visual in the report.

Because if you don't select any date from Report As At, the calculation is calculated for the whole table. Unless you set the slicer must select at least one value.

 

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for your reply

 

Can you please try and access this link? I have never tried sharing a link outside of my office networkimage.png

 

 

Basically, when the"report as at" slicer is unselected (refer image above), the visual ideally must display the status of only 3 projects as one of them closed in the previous reporting period and is no longer required in the current cycle. Likewise, selecting a past reporting cycle, such as 31/08/2018, should display all 4 projects

 

So if a project is selected, it should display the status for the current reporting period. The problem I am facing is that, selecting a single project, e.g Project 3 which was reporting RED in August and AMBER in September, shows both the categories in the visual (as below) instead of only AMBER as that was what Project 3 was reported in September

 

image.png

 

Below is a screenshot of the raw data incase you cannot access the link. There isn't any confidential information in the file

image.png

 

 

Hope that helped

 

Thank you

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.