Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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 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!

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 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
      6 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
      6 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.