Cumulative Budget Starting at Zero

[Anonymous]

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 first transaction. Some details for context:

• 'Fusion Project Actuals' --> This table contains details on actual project transactions, with the following relevant variables:
  • 'Fusion Project Actuals'[Accounting Date] --> Gives the date for each specific transaction within a project
  •  'Fusion Project Actuals'[Amount] --> The amount of spend on each transaction for a specific project
•  'Fusion Project Details' --> This table gives project details primarily revolving around budget  
  
  • 'Fusion Project Details'[Project Start Date] --> This is the date a project was opened, BEFORE the first transaction ([Accounting Date]) (i.e., actual spend at this date is always zero)
  • For the visual, I created a measure to track cumulative spend based off [Amount] (X axis uses Accounting Date, Y axis uses Amount). The code for the measure is as follows:

Cumulative Total Spend =

VAR MaxDate = MAX('Fusion Project Actuals'[Accounting Date])
VAR Result =
CALCULATE(
     SUM('Fusion Project Actuals'[Amount]),
       'Fusion Project Actuals'[Accounting Date] <= MaxDate,
       ALL ('Fusion Project Actuals'[Accounting Date]))
RETURN
Result

------------------------------------------------------------------------------------------

The graph returns cumulative spend correctly, but I would like to have it begin with zero at [Project Start Date] on the x axis, rather than the first [Amount] at [Accounting Date]. Any help is much appreciated. Thanks!

[amitchandak]

@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]

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

[Anonymous]

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"