Forum Discussion

JBI's avatar
JBI
Frequent Visitor
7 years ago
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.   ...
Need Help
  • Iamnvt's avatar
    Iamnvt
    7 years ago

    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

    https://1drv.ms/u/s!Aps8poidQa5zk6pvkzxCUW8XcB24JA

Iamnvt's avatar
Iamnvt
Icon for Continued Contributor rankContinued Contributor
7 years ago

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

https://1drv.ms/u/s!Aps8poidQa5zk6pvkzxCUW8XcB24JA