Is it possible to ignore the year part of a MonthYear slicer and keep the filter on the month?

[charles_g]

Hello,

My date table has Month, Year and MonthYear columns.

I use the MonthYear column in a slicer e.g. the user can select August 2021, September 2021...

In some visuals on the page, as well as in tooltips, I would like to show values for the selected month but in previous years.

Is there a way to ignore the year part of the slicer and keep the month part?

I know I could use 2 diferent slicers for the year and the month, but I find this solution less elegant.

Thanks for any help!

[AlexisOlson · Accepted solution]

If you have a date parameter table that you use for your slicer that is not related to other tables, you could write measures that read in the selected slicer value and apply whatever filtering you choose based on that selection. This isn't how date dimension tables are usually set up though (you usually want to have a relationship that filters your fact tables).

Your measure might look something like this:

DateSlicedMeasure =
VAR SelectedDate = SELECTEDVALUE ( DateSlicer[MonthYear] )
RETURN
    CALCULATE ( [Measure], DateTable[Month] = MONTH ( SelectedDate ) )

[charles_g]

Thank you, that worked!

Here are the steps I took based on your suggestion :

1. Create a new table to use in the slicer

MonthNumber and relativeMonthIndex are used for sorting

MonthYearSlicer = 
    SUMMARIZE(
        'Calendar',
        'Calendar'[MonthYear], 
        'Calendar'[Year], 
        'Calendar'[Month], 
        'Calendar'[MonthNumber], 
        'Calendar'[relativeMonthIndex]
     )

2. Create a calculation group with the following calculated items to transfer the filter I want from MonthYearSlicer to my Calendar table. (with a calculation group I don't need to rewrite all my measures)

keepMonth =
    CALCULATE ( 
        SELECTEDMEASURE (), 
        TREATAS ( 
             VALUES(MontYearSlicer[Month]), 
             Calendar[Month] 
         ) 
    )

keepMonthYear= 
    CALCULATE ( 
        SELECTEDMEASURE (), 
        TREATAS ( 
            VALUES(MonthYearSlicer[MonthYear], 
            Calendar[MonthYear]
        ) 
    )

3. Replace Calendar[MonthYear] by MonthYearSlicer[MonthYear] in the slicer

4. Add the new calculation group in the filter pane for each visual.

5. Select keepMonth for the visuals where I want to display other years, keepMonthYear for the other visuals

[AlexisOlson]

Looks good! Thanks for detailing the steps for future readers.

[charles_g]

Thanks for your reply.

That works to get the value I want in a measure but it will not let me display a bar graph showing 2020 and 2021 for the selected month.

The measure will show the value for 2020 in 2021. The slicer still prevents other years from showing.

[PaulDBrown]

Just create measures to return the desired value. Along the lines of:

Previous year = CALCULATE (Your measure], DATEADD(Date[Date], -1, YEAR])