Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Running Total Formula - Stop calculation at the latest data available

Hi there, I have a measure cauculating my running total for hours spent:   Running Total Actual hr = CALCULATE(     SUM('4. Actual Manpower Forecast'[Actual hr]),     FILTER(         ALLSELE...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Running = 
CALCULATE ( SUM ( 'Table'[Actual hr] ),
    FILTER ( ALL ( 'Table' ), [Week Date] <= SELECTEDVALUE ( 'Table'[Week Date] ) )
)
    Result = 
 Var _maxtotal=MAXX(ALL('Table'),[Running])
 Var _lastdate=CALCULATE(MIN('Table'[Week Date]),FILTER(ALL('Table'),[Running]=_maxtotal))
 Return
 IF(SELECTEDVALUE('Table'[Week Date])>_lastdate,BLANK(),[Running])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

v-zhangti's avatar
v-zhangti
Community Support
3 years ago

Hi, Anonymous 

 

You can try the following methods.

Measure:

Running = 
CALCULATE ( SUM ( 'Table'[Actual hr] ),
    FILTER ( ALL ( 'Table' ), [Week Date] <= SELECTEDVALUE ( 'Table'[Week Date] ) )
)
Result = 
 Var _maxtotal=MAXX(ALL('Table'),[Running])
 Var _lastdate=CALCULATE(MIN('Table'[Week Date]),FILTER(ALL('Table'),[Running]=_maxtotal))
 Return
 IF(SELECTEDVALUE('Table'[Week Date])>_lastdate,BLANK(),[Running])

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

That worked perfect! Thanks v-zhangti !