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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
moldfield
Frequent Visitor

Decreasing Running Total Measure with Date Hierarchy

Hello,

 

I have a running total measure of costs with an end date and the quick measure function with some small changes gives a correct output.

1.png

function.png

I'd like to represent this in Quarters, however using the date hierarchy to do so runs into a problem as we don't have items expiring in every quarter, so when it hits an empty one it defaults to the max value as shown. This is a particular problem if I filter down into item type as there are many more empty quarters.

2.png

 The desired output would be like this, giving the previous Quarter's value if there were no end dates in that Quarter.

3.png

Can the dax measure be changed to get this output? The relevant data columns I have are an identfying serial number, asset type, lease start date, lease end date and the quarterly lease cost.

 

Thanks.

 

edit: Formula should be SUM, not COUNTA. I've changed this but it does not affect any of the outputs.

1 ACCEPTED SOLUTION

Nevermind, used the wrong columns.

quarterly.png

 This is what I wanted.

View solution in original post

4 REPLIES 4
moldfield
Frequent Visitor

Another thing I've tried was creating a table with all the years and quarters then doing a Full Outer Merge to give me the null Quarters then remake the measure, which is closer but now it's omitting the empty Quarters even if I select the "Show items with no Data" option in the visual". 

quarters.png

func.png

Out of ideas.  

Nevermind, used the wrong columns.

quarterly.png

 This is what I wanted.

amitchandak
Super User
Super User

@moldfield , Try a measure like the example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] >=max('Date'[date])))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you for the response. The difference between this example (which I think is the default running total measure) and mine is the date column in the all selected and the max instead of min (since I need a decreasing total). Adding the column back in to the filter doesn't look right.

new measure.png

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.