cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Tutorial Series Dax Vs SQL Direct Query PBI Tips

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"

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors