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 until it reaches a specific threshold value which is currently stored as a Measure as well.

 

Any ideas on how best to solve for this?

 

Thanks!

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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Running Total = 

      <current code>

    RETURN

      <something>

     

    to

     

    Running Total =

      <current code>

      VAR __runningtotal = <something>

    RETURN

      IF(__runningtotal >= [Threshold Measure],[Threshold Measure],__runningtotal)

  • 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!