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
Chachi
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 

Chachi_0-1712153438492.png

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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:
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

View solution in original post

v-xuxinyi-msft
Community Support
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.

vxuxinyimsft_1-1712212652223.png

 

vxuxinyimsft_2-1712212692994.png

 

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.

vxuxinyimsft_3-1712213011677.png

 

Result:

vxuxinyimsft_4-1712213050311.png

 

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.

View solution in original post

2 REPLIES 2
v-xuxinyi-msft
Community Support
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.

vxuxinyimsft_1-1712212652223.png

 

vxuxinyimsft_2-1712212692994.png

 

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.

vxuxinyimsft_3-1712213011677.png

 

Result:

vxuxinyimsft_4-1712213050311.png

 

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
Super User
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:
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

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.