Running Total with nested calculation

[kpickle]

Hi there, 

Is there a way to add a nested calculation within a running total? 

Example:

When the running total hits 1,000 subtract 900 and continue the running total...

Would it better to try this as a measure or a calculated column?

Thank you.

[ryan_mayu]

@kpickle 

agree with @PhilipTreacy , measure is better.

you can try his solution

if you want to create a column, you can try this

Column = 
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
return _SUM-_reach*900

[kpickle]

Hey Guys, 

I tried it both ways and a couple issues - both work for the first trigger. 

As a measure:

Question 1 - is there any logic I can add behind the measure to calculate off the previous days result instead of SUM?

As a calcualted column:

Question 2 - Is there logic behind calculating the integer and rounding it up somehow? This seems like the more comlplex solution though.

I need to take a mental break from this so here is some sample data if anyone wants to take a crack at it. Thank you!

Date	Daily	SUM	reach_INT	Test1 (column)	Test2 (measure)	CORRECT	Trigger	Decrease
9/26/2022	105	105	0	105	105	105	1000	900
9/27/2022	100	205	0	205	205	205
9/28/2022	125	330	0	330	330	330
9/29/2022	150	480	0	480	480	480
9/30/2022	115	595	0	595	595	595
10/1/2022	160	755	0	755	755	755
10/2/2022	110	865	0	865	865	865
10/3/2022	200	1065	1	165	165	165
10/4/2022	100	1165	1	265	265	265
10/5/2022	205	1370	1	470	470	470
10/6/2022	180	1550	1	650	650	650
10/7/2022	110	1660	1	760	760	760
10/8/2022	190	1850	1	950	950	950
10/9/2022	170	2020	2	220	1120	220
10/10/2022	180	2200	2	400	1300	400
10/11/2022	195	2395	2	595	1495	595
10/12/2022	115	2510	2	710	1610	710
10/13/2022	115	2625	2	825	1725	825
10/14/2022	160	2785	2	985	1885	985
10/15/2022	110	2895	2	1095	1995	195
10/16/2022	200	3095	3	395	2195	395
10/17/2022	100	3195	3	495	2295	495
10/18/2022	205	3400	3	700	2500	700
10/19/2022	180	3580	3	880	2680	880
10/20/2022	110	3690	3	990	2790	990
10/21/2022	190	3880	3	1180	2980	280
10/22/2022	180	4060	4	460	3160	460
10/23/2022	110	4170	4	570	3270	570
10/24/2022	190	4360	4	760	3460	760
10/25/2022	170	4530	4	930	3630	930
10/26/2022	160	4690	4	1090	3790	190
10/27/2022	110	4800	4	1200	3900	300

[ryan_mayu · Accepted solution]

@kpickle 

you can update the DAX like below

Column = 
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
VAR _result=_SUM-_reach*900
return if (_result>1000,_result-900,_result)

[kpickle]

Works beautifully. Thank you both!

[ryan_mayu]

you are welcome

[PhilipTreacy]

Hi @kpickle 

Yes.  Let's say you are calcuting the RT like this (a measure, this is better than a static calculated column)

= CALCULATE(SUM([Actual]), FILTER(ALLSELECTED('Calendar'[Date]),ISONORAFTER('Calendar'[Date], MAX(Actual[Date]), DESC)))

You can modify that to check the value of the running total (storing it in a Variable called _RT) and carry out whatever modification you want based on the value of _RT e.g.

= VAR _RT = CALCULATE(SUM([Actual]), FILTER(ALLSELECTED('Calendar'[Date]),ISONORAFTER('Calendar'[Date], MAX(Actual[Date]), DESC)))

RETURN

IF(_RT > 1000, _RT - 1000, _RT)

Regards

Phil