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

    For some reason PBI won't recognize my first reference to Project Number. For reference, this variable is stored in text format because the id's are formatted as "S12345"

     

     

     

     

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