Forum Discussion
Running Total for above n rows
Hi everyone,
I have a table of daily sales, and Need to calculate the running total of current day + previous 4 days ..
Example below:
Any help or advice please ?
Anonymous , Create a measure like, Prefer date tbale
Rolling 4 = CALCULATE(Sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,DAY))
or
Rolling 4 = CALCULATE(Sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,DAY))
4 Replies
- amitchandak
Super User
Anonymous , Create a measure like, Prefer date tbale
Rolling 4 = CALCULATE(Sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,DAY))
or
Rolling 4 = CALCULATE(Sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,DAY))
- jeroendekk
Responsive Resident
Hi Anonymous
First you will probably need a datetable in your model. After that you can achieve this with the DATESINPERIOD function in a measure like this.Current and Previous 4 days = CALCULATE( SUM('Table'[Sales]), DATESINPERIOD(Datetable[Date], MAX(Datetable[Date]), -5, DAY))If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
Best regards,
Jeroen - MFelix
Hi Anonymous ,
You need to create a measure that picks up the last date try something similar to:
Running 4 days = CALCULATE ( SUM ( TAble[Sales] ), FILTER ( ALL ( Table[Date] ), Table[Date] <= MAX ( Table[Date] ) && Table[Date] >= MAX ( Table[Date] ) - 4 ) )
