Forum Discussion

kpickle's avatar
kpickle
Frequent Visitor
3 years ago
Solved

Running Total with nested calculation

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 t...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    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)
PhilipTreacy's avatar
PhilipTreacy
Super User
3 years ago

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