Dax function DATESINPERIOD not giving correct answer

Question

I need to find moving two days sum of sales. I am using DAX function DatesinPeriod but the output is not coming correct. Please help me understand where I am going wrong please. I am using below Dax Formula:

Measure = CALCULATE(sum('Table'[Sale]),DATESINPERIOD('Dim Date'[Date],SELECTEDVALUE('Table'[Date]),-2,day))

To replicate the scenario first step is to create Dim Date table using - >

Dim Date = GENERATESERIES(date(2019,01,01),date(2019,12,31),1)

second Step is to create DataTable ->

Table = DATATABLE("Date",DATETIME,"Flag1",STRING,"Flag2",STRING,"Sale",INTEGER,{
{"8/1/2019","True","True",200},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"9/3/2019","False","True",60},
{"9/4/2019","False","True",10},
{"9/5/2019","False","True",100},
{"9/6/2019","False","True",30},
{"9/7/2019","False","True",60},
{"9/8/2019","False","False",150},
{"9/9/2019","False","False",80},
{"9/10/2019","False","False",90},
{"9/11/2019","False","False",30},
{"9/12/2019","False","False",20},
{"10/13/2019","False","True",50},
{"10/14/2019","False","True",60},
{"10/15/2019",BLANK(),BLANK(),BLANK()},
{"10/16/2019",BLANK(),BLANK(),BLANK()}
})

3rd Step - create a relation between these tables on date column

4th step - create Measure using - Measure = CALCULATE(sum('Table'[Sale]),DATESINPERIOD('Dim Date'[Date],SELECTEDVALUE('Table'[Date]),-2,day))

You will see the output coming wrong. see the screenshot. This is very strange. I tried using DatesBetween function , its also giving me the same wrong output.

Agustin Palacios Agustin Palacios · Accepted Answer · 2020-08-09T00:37:09

Use the following measure to obtain the expected result:

SumInRange = 
VAR __selectedDate = SELECTEDVALUE( 'Table'[Date] )
VAR __subTable = 
    FILTER( 
        ALL( 'Table'[Date] ),
        AND(
            'Table'[Date] >= __selectedDate -2,
            'Table'[Date] <=__selectedDate
        )
    )

Return
CALCULATE( 
    SUMX (
        DISTINCT ( 'Table'[Date] ),
        CALCULATE ( MAX ( 'Table'[Sale] ) )
    ),
    __subTable
)

Be sure to use the Date column from Table instead of the Dim in the visualization.

Dax function DATESINPERIOD not giving correct answer

1 Answers