Running Total Help Needed Please

[stokidez]

Hi,

I have a report which looks at the number of projects starting and closing over a period of time. This is simply looking at two different fields, Project Planned Start Date and Planned Contract Signed Date. There is a separate Date table which is linked to the main Project table.

I would like to add a third value to this visual which is a running total of current live projects. We have a status field in the dataset which would indicate whether a project is live or not and as above we could use the Planned Contract Signed Date to forecast whether a project would still be running in that particular month.

Could someone give me a hand with how to make this happen please? In layman terms, this is how I see the calculation:

Today	Count of Total Projects where Status = Live
1st June	Figure above minus any projects due to complete within the timeframe of today and 1st June
1st July	Figure above minus any projects due to complete within the timeframe of 1st June and 1st July
1st August	Figure above minus any projects due to complete within the timeframe of 1st July and 1st August
1st Sept	Figure above minus any projects due to complete within the timeframe of 1st August and 1st Sept
etc…..

Thanks in advance!

[Anonymous]

Hi @stokidez ,

Check the measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Project Title]),FILTER(ALL('Table'),'Table'[Planned Contract Signed Date]>=SELECTEDVALUE('calendar'[date])))

Best Regards,

Jay

[stokidez]

Thanks @Anonymous 

I've added the measure as follows:

Running Total Live Projects = CALCULATE(DISTINCTCOUNT('Procurement Plan'[Contract Title]),FILTER(ALL('Procurement Plan'),'Procurement Plan'[Planned Contract Signed Date]>=SELECTEDVALUE('Date Table'[Date])))

However, despite there being projects with a Contract Signed Date, the number does not reduce. Where am I going wrong please?

[Anonymous]

Hi @stokidez ,

I don't get the points through the screenshot, could you share the .pbix if you don't have any Confidential Information.

Best Regards,

Jay

[amitchandak]

@stokidez ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

But I think this blog should help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[stokidez]

No worries @amitchandak , here is a very basic sample dataset. Assume the Projects listed below is the entire portfolio (10 Projects).

Project Title     Planned Contract Signed Date
Project A         29th May
Project B         27th August 2021
Project C         12th July 2021
Project D         20th September
Project E         3rd September
Project F          2nd September
Project G         25th May
Project H         8th July
Project I          16th July
Project J          14th June

What I would like to show on the first of every month in the visual is the number of current live projects. We can assume that if the date has not passed the Planned Contract Signed Date then the project is still live. Therefore, as an output I would expect to see.......

Date                    Number of Live Projects              Remarks
May 1st               10
June 1st               8                                                  Project A and Project G have completed
July 1st                7                                                  Project A, G and J have completed
August 1st           4                                                  Project A, G, J, C, H and I have completed
September 1st     3                                                  Project A, G, J, C, H, I and B have completed
October 1st          0                                                 Project A, G, J, C, H, I, B, D, E, F have completed