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)
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)
PattemManohar - you are a god! :)
Thank you so much for your time and your help - it's a solution I really needed, thank you!
:)
PattemManohar do you jnow how to do this if the sales for the different years were in different tables?
Anonymous If you have year wise sales in different tables, then append those into single table using "Append" in Power Query or use "UNION" function in DAX. That will result into single table with overallsales data at one place.