Rolling total based on date -20 days

[athomp15]

Hi All,

I'm struggling to work out (so seeking help for) a Power Query or DAX Calc, to work the maximum sales value is for each Employee based on a 20 day rolling period.  The data table is shown below.  So for Employee 1, it would be dates 27/01/2018 and 14/02/2018 with a total sales for 120000

Employee, Date, Sales

Employee1, 20/01/2018,10000

Employee1, 27/01/2018,30000

Employee1, 14/02/2018,90000

Employee1, 11/03/2018,50000

Employee1, 19/03/2018,20000

Employee1, 20/03/2018,4000

Employee2, 20/01/2018,2000

Employee2, 22/02/2018,3000

Employee2, 24/02/2018,60000

Employee2, 07/03/2018,50000

Employee3, 28/02/2018,1000

Employee3, 20/03/2018,70000

Thanks in advance

Alex

[athomp15]

Works Perfectly MFelix, 

Many thanks

Alex

[MFelix · Accepted solution]

Hi @athomp15,

Try the formula below.

Rolling 20 days =
CALCULATE (
    SUM ( Sales[ Sales] );
    FILTER (
        ALL ( Sales[ Date] );
        Sales[ Date]
            >= MAX ( Sales[ Date] ) - 20
            && Sales[ Date] <= MAX ( Sales[ Date] )
    )
)

Regards,

MFelix