Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter 3 months based on slicer selection

Hello all,   I have a matrix based on a datasource and I need to show 3 months based on a slicer selection. I have 2 slicers, one showing years and one showing month names. The requriement is that ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    This did help in a way. There was a comment pointing to another video which helped me do this. Essentially my approach was to use Tabular Editor to create the filter which is applied to any measure, which was my preference.

    The video covers that in the second half as the first half is about explaining the formula and which fields to use in the visual and slicers:

    https://www.youtube.com/watch?v=d8Rm7dwM6gc&ab_channel=SQLBI

    You need to create a Date table and then duplicate that. My final formula is this:

     

    VAR NumofMonths = 3
     VAR ReferenceDate = MIN('Date Table 1'[Date])
     VAR SecondaryDate = 
        DATESINPERIOD(
            'Date Table 2'[Date],
            ReferenceDate,
            NumofMonths,
            MONTH
        )
    VAR Result = 
        CALCULATE(
            SELECTEDMEASURE(),
            REMOVEFILTERS('Date Table 1'),
            KEEPFILTERS(SecondaryDate),
            USERELATIONSHIP('Date Table 1'[Date],'Date Table 2'[Date])
        )
     RETURN
        Result

     

    As i was looking at a slicer to show me dates in the future i used MIN for my ReferenceDate. If looking for dates in the past then use MAX I think.