Dynamic date filter not working

[JimJim]

Hi Guys,

I have attached a link to a pbix which includes a month slicer and a matrix, I would like to select one month from the slicer and have the matrix display only 3 months of data up to and including the selected month.

I followed along a tutorial by Alberto Ferrari but no matter what I do I cannot get my filter to show only 3 months. The problem appears to be with function DATESBETWEEN which is returning too many dates, I was not able to use DATESINPERIOD as I am using a fiscal calendar.

On the attached pbix, please see page 'dynamic date', the measure is named Quote Count R3M

https://1drv.ms/u/s!ArazqhFmvkSJlmVxJRRpSR1dAG4m?e=PUBSX4

Thanks 🙂

[v-henryk-mstf]

Hi @JimJim ,

Is it possible to explain the logic of the formula you created, it seems that the filter function is causing the problem. Looking forward to your reply.

Best Regards,
Henry

[JimJim]

Hi @v-henryk-mstf , thank you.

I am trying to allow a user to select a month from a slicer and show them data for x months up to and including the selected month. I am using a fiscal calendar

The demo I followed is https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/, however, Alberto uses whole months in his calculation and I'm not able to do this as I'm using a fiscal calendar. Therefore my logic is trying to work out the first date and last date and and show quotes that fall between both dates.

[JimJim]

I have also tried to use a days instead of whole months but this also does not return the expected results:

Weirdly, when I replace the __days variable with a literal, such as -91, it works, but as soon as I pass in the __days variable it stops working again

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 ( 'Previous Time'[Date] ),
        'Previous Time'[YearMonthKey] = __financialPeriod )
// calculate number of days
VAR __days = (DATEDIFF(__fromDate, __selectedDate, DAY) + 1 ) * -1
// create a date table        
VAR __dates = 
        DATESINPERIOD('Previous Time'[date],
        __selectedDate,
        __days,
        DAY)
VAR __result = 
        CALCULATE([Quote Count],
            REMOVEFILTERS('time'),
            KEEPFILTERS(__dates),
            USERELATIONSHIP('Previous Time'[date], 'time'[date]))
RETURN
__result