Declining line chart ovetime and date when value is equal to 0

[Chachi]

Hi All,

I'm working on creating a declining  line chart in Power BI to visualize my obligated budget over time. Here's what I have: a table with a calculated field called 'Obligated Amount' representing the total sum of obligated funds. Additionally, I have calculated fields for 'Expended Amount' and 'Remaining Amount.'

Now, I want to create a declining line chart where the total obligated amount is shown as starting point, and the line depicts how much of the obligated amount remains over time by deducting expended amounts from the previous remaining amount everytime. The line should visibly decline as funds are expended.

Also, I need to calculate when I'm expected to run out of obligated funds. Can anyone guide me on how to achieve this in Power BI? Specifically, how can I create the declining chart and calculate the projected date when the funds will be depleted so I can show the date on KPI card?

I posted a sample data and what im expecting the data to look like below. Please provide any help you can 

[amitchandak · Accepted solution]

@Chachi , if you have total amount from which we need remaining , using a date table

calculate(Sum(Table[Amount]), allselected(Date)) - calculate(Sum(Table[Expanded]), Filter(all(Date), Date[Date]<= Max(Date[Date]) ) )

or just adding up everything in the future

calculate(Sum(Table[Expanded]), Filter(all(Date), Date[Date]>= Max(Date[Date]) ) )

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[v-xuxinyi-msft · Accepted solution]

Hi @Chachi 

Thanks for the reply from @amitchandak , please allow me to provide another insight:

First of all regarding the data you provided I am a little confused as to why April is followed by May, in the tests I have done I have modified the months to be as shown below, if I have misunderstood you please point out and I will modify what I did.

My testing:

1. Create a measure as follows

sum = SUM(TableA[obligation])

2. Create two calculated column as follow

maxMY = MAX([month-year]) 

Remaining = 
VAR _earlier = 'TableB'[month-year]
VAR _lastEx = CALCULATE(SUM(TableB[Expanded]), FILTER(TableB, _earlier < [month-year]))
RETURN
IF([month-year] = [maxMY], [sum], [sum] - _lastEx)

You can also modify the maximum and minimum values of the Y-axis here.

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Chachi , if you have total amount from which we need remaining , using a date table

calculate(Sum(Table[Amount]), allselected(Date)) - calculate(Sum(Table[Expanded]), Filter(all(Date), Date[Date]<= Max(Date[Date]) ) )

or just adding up everything in the future

calculate(Sum(Table[Expanded]), Filter(all(Date), Date[Date]>= Max(Date[Date]) ) )

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f