Issue with DATESINPERIOD

[JimJim]

Hi Guys,

Firstly, I am sorry for the lack of test data or even a pbix file, I tried replicating the issue in a test report so that I could demonstrate it to you, but I can't replicate the issue.

The problem:

Across many of our reports, we have a DAX function that is used throughout, it allows a user to select a month from a slicer and then a chart displays a rolling 13 months from the selected date. The DAX to do this is:

Quote Count Dynamic 13M = 
// get the selected date
VAR __selectedDate =
    MAX ( 'Time'[Date] )
// create a date table        
VAR __dates = 
        DATESINPERIOD('Time (previous months)'[CalendarDate],
        __selectedDate,
        -13,
        MONTH)
VAR __result = 
        CALCULATE([Quote Count],
            REMOVEFILTERS('time'),
            KEEPFILTERS(__dates),
            USERELATIONSHIP('Time (previous months)'[date], 'time'[date]))
RETURN
__result

Suddenly, when a user selects Dec-2023 the chart is blank, but strangely this isn't happening in all of our reports. There is no consistency, the DAX is identical in all report as is the relationship between date tables. 

I have identified that it is the __dates variable that returns no data when a user selects Dec-2023, for Nov-2023 __dates has 13 rows, but for Dec-2023 __dates is null. 

Does anyone have any suggestions?

[Ahmedx · Accepted solution]

replace this piece of code with the following

VAR __dates = 
        DATESINPERIOD('Time (previous months)'[CalendarDate],
        __selectedDate,
        -13,
        MONTH)

to

VAR __dates = 
        DATESBETWEEN('Time (previous months)'[CalendarDate]
        ,EDATE(__selectedDate,-13),__selectedDate)

[JimJim]

@Ahmedx , thank you. this works. But how? Is it a bug or am I being stupid?

[Ahmedx]

refer to the following

https://www.youtube.com/watch?v=ED1g4-RvLOs&ab_channel=taik18-MohammedAdnan

[Ahmedx]

replace this piece of code with the following

VAR __dates = 
        DATESINPERIOD('Time (previous months)'[CalendarDate],
        __selectedDate,
        -13,
        MONTH)

to

VAR __dates = 
        DATESBETWEEN('Time (previous months)'[CalendarDate]
        ,EDATE(__selectedDate,-13),__selectedDate)