cancel
Showing results for
Did you mean:

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

Frequent Visitor

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

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

2 ACCEPTED SOLUTIONS
Super User

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

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

Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors