Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Cumulative Budget Starting at Zero

I am trying to put together a visual for cumulative spend over time on a project. So far I have been able to get a chart based on actuals, but would like to have spend start at zero rather than the f...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , try a mesure like , assumed you have project or project id

 

Cumulative Total Spend =

VAR MaxDate = MAX('Fusion Project Actuals'[Accounting Date]

RETURN
CALCULATE(
SUM('Fusion Project Actuals'[Amount]),
filter( ALL ('Fusion Project Actuals'[Accounting Date]) ,'Fusion Project Actuals'[Accounting Date] <= MaxDate
&& 'Fusion Project Actuals'[project] = max( 'Fusion Project Actuals'[project] )
))

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak -- to try and achieve this, I have created a new table called 'Fusion Project Actuals - Cumulative' that contains the fields Project Number, Transaction Date, Task Number, Amount. For each project number, there is now a row in this table that contains a transaction date as start date, and a first amount of zero. So the data is in there, but still having trouble getting the graphic to appear correctly. My code is currently as follows:

 

Cumulative Total Spend =

VAR MaxDate = MAX('Fusion Project Actuals - Cumulative'[Transaction Date])
VAR Result =
CALCULATE(
SUM('Fusion Project Actuals - Cumulative'[Amount]),
'Fusion Project Actuals - Cumulative'[Transaction Date] <= MaxDate,
ALL ('Fusion Project Actuals - Cumulative'[Transaction Date]))
RETURN
Result
 
 
 
Any ideas?? Thanks.