Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Running total with a specific start value

I have a problem regarding running totals:   1. I have sales data for 2017 and 2018 for every day 2. I want a running total of 2018 with the start value of "Sum of sales 2017"
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous Please try this as a "New Measure"

     

    Test207 = 
VAR _2017Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2017))
VAR _2018Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2018 && Test207RunningTotal[Date] <= MAX(Test207RunningTotal[Date])))
RETURN IF(MAX(Test207RunningTotal[Year])=2018,_2017Sales + _2018Sales)

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

Anonymous  Please try this as a New Column

 

RunningTotal = 
VAR _2017Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2017))
VAR _2018Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2018 && Test207RunningTotal[Date] <= EARLIER(Test207RunningTotal[Date])))
RETURN IF(Test207RunningTotal[Year]=2018, _2017Sales+_2018Sales)

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you PattemManohar for your solution - I wonder if there is a generic solution with a measure since I prefer not to have a calculated column!

    /Sebastian

    • PattemManohar's avatar
      PattemManohar
      Community Champion
      7 years ago

      Anonymous Please try this as a "New Measure"

       

      Test207 = 
VAR _2017Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2017))
VAR _2018Sales = CALCULATE(SUM(Test207RunningTotal[Sales]),FILTER(ALL(Test207RunningTotal),Test207RunningTotal[Year]=2018 && Test207RunningTotal[Date] <= MAX(Test207RunningTotal[Date])))
RETURN IF(MAX(Test207RunningTotal[Year])=2018,_2017Sales + _2018Sales)

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        PattemManohar - you are a god! :)

        Thank you so much for your time and your help - it's a solution I really needed, thank you!

        :)