Forum Discussion

mreese23's avatar
mreese23
Frequent Visitor
6 years ago
Solved

Limit Running Total Once it Reaches Target Value

Hello,   I am calculating a Monthly Forecast and displaying it as a Running Total line chart. The Running Total is a Measure. I would like to have the Running Total display all values/months up unt...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi mreese23 ,

    I create this table to calculate each moth total sales as a sample:

    Previous Running total measure:

    Running Total =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    ALLEXCEPT ( 'Table', 'Table'[Date].[MonthNo] )
)

    New Running total measure:

    New Running total =
VAR _rt = [Running Total]
VAR _Threshold = 800 //It could also be a measure formula
RETURN
    IF ( _rt >= _Threshold, _Threshold, _rt )

    Two results of the line chart as a comparison:

     

    Best Regards,
    Yingjie Li

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

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi mreese23 ,

I create this table to calculate each moth total sales as a sample:

Previous Running total measure:

Running Total =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    ALLEXCEPT ( 'Table', 'Table'[Date].[MonthNo] )
)

New Running total measure:

New Running total =
VAR _rt = [Running Total]
VAR _Threshold = 800 //It could also be a measure formula
RETURN
    IF ( _rt >= _Threshold, _Threshold, _rt )

Two results of the line chart as a comparison:

 

Best Regards,
Yingjie Li

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

mreese23's avatar
mreese23
Frequent Visitor
6 years ago

Thanks v-yingjl  & Greg_Deckler similar solutions that both worked great!

 

I wanted the Running total chart to end once it reached the threshold instead of flattening so I modified the solution to this:

New Running total =
VAR _rt = [Running Total]
VAR _Threshold = [Threshold Measure]
RETURN
    IF ( _rt >= _Threshold, -1, _rt )

 Then I filter the views to be [New Running total] > 0.

 

Thanks again!