Forum Discussion

JimJim's avatar
JimJim
Responsive Resident
4 years ago

Dynamic date filter not working

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 🙂

Need Help

3 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    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's avatar
      JimJim
      Responsive Resident
      3 years ago

      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's avatar
    JimJim
    Responsive Resident
    4 years ago

    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