topic Re: Decreasing Running Total Measure with Date Hierarchy in DAX Commands and Tips

Decreasing Running Total Measure with Date Hierarchy

moldfield — Tue, 01 Feb 2022 14:55:11 GMT

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.

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.

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

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.

Re: Decreasing Running Total Measure with Date Hierarchy

amitchandak — Tue, 01 Feb 2022 15:14:08 GMT

moldfield , Try a measure like the example

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

Re: Decreasing Running Total Measure with Date Hierarchy

moldfield — Tue, 01 Feb 2022 15:26:35 GMT

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.

Re: Decreasing Running Total Measure with Date Hierarchy

moldfield — Tue, 01 Feb 2022 16:11:17 GMT

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".

Out of ideas.

Re: Decreasing Running Total Measure with Date Hierarchy

moldfield — Tue, 01 Feb 2022 16:17:58 GMT

Nevermind, used the wrong columns.

This is what I wanted.