Forum Discussion
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"
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)
6 Replies
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)
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
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)