Running Total Help Needed Please

Question

Hi,&nbsp;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.&nbsp;&nbsp;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.&nbsp;Could someone give me a hand with how to make this happen please? In layman terms, this is how I see the calculation:&nbsp;TodayCount of Total Projects where Status = Live1st JuneFigure above minus any projects due to complete within the timeframe of today and 1st June1st JulyFigure above minus any projects due to complete within the timeframe of 1st June and 1st July1st AugustFigure above minus any projects due to complete within the timeframe of 1st July and 1st August1st SeptFigure above minus any projects due to complete within the timeframe of 1st August and 1st Septetc…..&nbsp;&nbsp;Thanks in advance!

amitchandak · Answer

stokidez ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
&nbsp;
But I think this blog should help
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

stokidez · Answer

No worries amitchandak&nbsp;, here is a very basic sample dataset. Assume the Projects listed below is the entire portfolio (10 Projects).Project Title&nbsp; &nbsp; &nbsp;Planned Contract Signed DateProject A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;29th MayProject B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27th August 2021Project C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12th July 2021Project D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20th SeptemberProject E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3rd SeptemberProject F&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2nd SeptemberProject G&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25th MayProject H&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8th JulyProject I&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 16th JulyProject J&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14th JuneWhat 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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Number of Live Projects&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RemarksMay 1st&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10June 1st&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Project A and Project G have completedJuly 1st&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Project A, G and J have completedAugust 1st&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Project A, G, J, C, H and I have completedSeptember 1st&nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Project A, G, J, C, H, I and B have completedOctober 1st&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Project A, G, J, C, H, I, B, D, E, F have completed&nbsp;

anonymous · Answer

Hi&nbsp;stokidez&nbsp;,
&nbsp;
Check the measure.
Measure = CALCULATE(DISTINCTCOUNT('Table'[Project Title]),FILTER(ALL('Table'),'Table'[Planned Contract Signed Date]&gt;=SELECTEDVALUE('calendar'[date])))

&nbsp;
Best Regards,
Jay

stokidez · Answer

Thanks&nbsp;Anonymous&nbsp;&nbsp;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]&gt;=SELECTEDVALUE('Date Table'[Date])))&nbsp;However, despite there being projects with a Contract Signed Date, the number does not reduce. Where am I going wrong please?&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;stokidez&nbsp;,
&nbsp;
I don't get the points through the screenshot, could you share the .pbix if you don't have any&nbsp;Confidential Information.
&nbsp;
Best Regards,
Jay

Forum Discussion

Running Total Help Needed Please

5 Replies

Related content

Service Error - Need Help Please!

Error Power BI Service - Need Help Please!

Running Maximum

Running sum

Pipeline not running

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…..