cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Olivecrona Regular Visitor
Regular Visitor

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"

running total problem.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Running total with a specific start value

@Olivecrona 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)

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





6 REPLIES 6
Super User
Super User

Re: Running total with a specific start value

@Olivecrona  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)

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Olivecrona Regular Visitor
Regular Visitor

Re: Running total with a specific start value

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

Super User
Super User

Re: Running total with a specific start value

@Olivecrona 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)

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Olivecrona Regular Visitor
Regular Visitor

Re: Running total with a specific start value

@PattemManohar - you are a god! Smiley Happy

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

Smiley Happy

Olivecrona Regular Visitor
Regular Visitor

Re: Running total with a specific start value

@PattemManohar do you jnow how to do this if the sales for the different years were in different tables?

Highlighted
Super User
Super User

Re: Running total with a specific start value

@Olivecrona  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.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !