DAX formula for sales sum for each day of previous n days

Question

Given a table FACT in Power BI with three columns Date, Category and Sales I am looking for a DAX function that for each day returns the sum of the sales of its previous n days.
Lets assume n = 2, that means for day 01/04/2020 my measure should return the sum of the sales of the days 01/02/2020 and 01/03/2020. Here is a small example:

Date        Category  Sales 
01/01/2020  A         1
01/01/2020  B         3
01/02/2020  B         2
01/03/2020  B         1
01/04/2020  A         0
01/05/2020  B         10
01/06/2020  B         7

What I want is

Date        MyMeasure 
01/01/2020  0 
01/02/2020  4
01/03/2020  6
01/04/2020  3
01/05/2020  1
01/06/2020  10

I later would then like to use the Category as a filter and in my case n is 365.

I tried the following

MyMeasure = 
VAR FROM_DATE =
    DATEADD ( FACT[DATE], 0, DAY )
VAR SALES_365 =
    CALCULATE (
        SUM ( FACT[SALES] ),
        DATESINPERIOD ( FACT[DATE], FROM_DATE, 365, DAY )
    )
RETURN
    SALES_365

but got an error that a table of multiple values was supplied where a single value was expected.

Giovanni Luisotto Giovanni Luisotto · Accepted Answer · 2020-10-27T23:52:18

The error was in the variable FROM_DATE, in which DATEADDD returned a column of dates and not a single value. That said you don't really need a variable for that, you can just use a simple subtraction and DATESBETWEEN(DateCol, DateFrom,DateTo)

MyMeasure = 
CALCULATE (
    SUM ( FACT[SALES] ),
    DATESBETWEEN('FACT'[DATE],MAX(FACT[DATE])-2,MAX(FACT[DATE])-1)
)

DAX formula for sales sum for each day of previous n days

1 Answers