DATESINPERIOD bug?

[Drax]

Hi Guys,

Here is a link to a pbix file, please see tab 'dynamic date' and measure Quote Count R3M: https://1drv.ms/u/s!ArazqhFmvkSJlmVxJRRpSR1dAG4m?e=Mt51q2

What I am trying to do is allow a user to select a month from the slicer and have the table show the previous 3 months of data (including the selected month).

I followed the alberto ferrari demo but had to tweak it a little as I'm using a fiscal calendar.

For the DATESINPERIOD function, when I pass in the number of days as a literal (such as -91) it works just fine, but when I pass in the value as a parameter (__days) I don't get the expected results.

Am I doing something really stupid here?

 

[Chihiro]

Issue isn't with __days alone. But data lineage between __fromDate & __days.

You can test this with replacing __fromDate with fixed date.

I suspect the issue is with 'Previous Time' table reference in both variables.

Try below.

Quote Count R3M =
// get the selected date
VAR __selectedDate =
    MAX ( 'Time'[Date] )
// get the financial key for selected date - 2 months   
VAR __financialPeriod =
    MIN ( 'Time'[YearMonthKey] ) - 2
// get the earliest date for __financialPeriod   
VAR __fromDate =
    CALCULATE (
        MIN ( 'time'[Date] ), FILTER(ALL('time'),
        'time'[YearMonthKey] = __financialPeriod ))
// calculate number of days
VAR __days = (DATEDIFF(__fromDate, __selectedDate, DAY) + 1 ) * -1
// create a date table       
VAR __dates =
        DATESINPERIOD('time'[date],
        __selectedDate,
        __days,
        DAY)
VAR __result =
        CALCULATE([Quote Count],
            REMOVEFILTERS('time'),
            KEEPFILTERS(__dates),
            USERELATIONSHIP('Previous Time'[date], 'time'[date]))
RETURN
__result

 

[Drax]

@Chihiro , thank you so much