Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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!

3 REPLIES 3
amitchandak
Super User
Super User

@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
Not applicable

@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
Not applicable

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"

 

 

 

 

code.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.