topic Re: Running Total based on end of value in another column in DAX Commands and Tips

Running Total based on end of value in another column

vai — Thu, 11 Apr 2024 18:48:55 GMT

Hello,

I have two running total measures:

1. RunningTotal Past Dates = if the date is less than today, then create a running total of values in actualized savings column based on approved dates.

2. Runnint Total Future dates: if the date is greater than or equal to today, then create a running total of values in future savings columns based on target dates.

I have created running total formulas for both and they work, but the issue is I want the running total of "2." to start with the end of running total past dates aka "1." For example, if we refer to the picture below, I want the running total of the second to be 13.74 + 0.35, instead of starting with 0. Is there any suggestions on achieving this?

Thank you!!

Re: Running Total based on end of value in another column

Greg_Deckler — Thu, 11 Apr 2024 18:52:36 GMT

vai This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Re: Running Total based on end of value in another column

Anonymous — Mon, 15 Apr 2024 05:34:42 GMT

Hi vai

Greg_Deckler Thank you very much for your prompt reply and here allow me to share my own method.

Here's some dummy data

"Table"

Create measures.

Running Total Past Dates = var _today = TODAY() var _TotalPast = CALCULATE( SUM('Table'[Values]), FILTER( ALL('Table'), 'Table'[Date] < _today ) ) RETURN IF( SELECTEDVALUE('Table'[Date]) < _today, _TotalPast, BLANK() )

Runnint Total Future dates = var _today = TODAY() var _TotalPast = CALCULATE( SUM('Table'[Values]), FILTER( ALL('Table'), 'Table'[Date] < _today ) ) var _TotalFuture = CALCULATE( SUM('Table'[Values]), FILTER( ALL('Table'), 'Table'[Date] >= _today ) ) RETURN IF( SELECTEDVALUE('Table'[Date]) >= _today, _TotalPast + _TotalFuture, BLANK() )

Here is the result.

Regards,

Nono Chen

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