Forum Discussion
Solved
Rolling Sum for 5 Days Only
Hi Guys,
I want to determine the rolling total of sales, taking only the previous 5 days into account. So if today is the 9th of July 2019, only data from the 5th until now. Rest to be zero.
Table (Goods sold) with Date and Sales columns. I need the measure to create "Previous 5 Days"
| Date | Sales | Previous 5 Days |
| 2019/07/01 | 10 | 0 |
| 2019/07/02 | 15 | 0 |
| 2019/07/03 | 20 | 0 |
| 2019/07/04 | 30 | 0 |
| 2019/07/05 | 40 | 40 |
| 2019/07/06 | 10 | 50 |
| 2019/07/07 | 20 | 70 |
| 2019/07/08 | 30 | 100 |
| 2019/07/09 | 5 | 105 |
Thanks in advance!
hi,
you can try this measure:
Prev 5 days = VAR maxdate = CALCULATE(MAX(Table1[Date]), ALL(Table1)) VAR currentselected = SELECTEDVALUE(Table1[Date]) VAR last5days = maxdate -5 VAR rolling5days = CALCULATE(SUM(Table1[Sales]), FILTER(ALL(Table1[Date]), Table1[Date] > last5days && Table1[Date] <= Currentselected)) RETURN rolling5days
here is the PBI file
2 Replies
cosmincPost Partisan
Hi
play with this:
https://community.powerbi.com/t5/Desktop/Rolling-average-on-calculated-table/m-p/693382#M334402
you need Calendar table marked as date table
hope it helps,
Cosmin
IamnvtContinued Contributor
hi,
you can try this measure:
Prev 5 days = VAR maxdate = CALCULATE(MAX(Table1[Date]), ALL(Table1)) VAR currentselected = SELECTEDVALUE(Table1[Date]) VAR last5days = maxdate -5 VAR rolling5days = CALCULATE(SUM(Table1[Sales]), FILTER(ALL(Table1[Date]), Table1[Date] > last5days && Table1[Date] <= Currentselected)) RETURN rolling5days
here is the PBI file