Relative date filter not working on Hierarchical slicer

[Anonymous]

Hi, new user here.

I have a hierarchical slicer in my report based on date hierarchy. It is currently showing as below:

What I would like is to eliminate any future quarters/months from the slicer. I.e., since it is now May 2021, I don't want June, Qtr 3 or Qtr 4 to appear in the slicer list for 2021.

I had a thought that this might be accomplished with relative date filters so I created the following report-level filter. However, this hasn't changed anything in the date slicer and future quarters/months are still showing there. Any thoughts on how to achieve this would be very much appreciated.

Thank you!

[amitchandak]

@Anonymous , Create a flag like this in your date table.

flag = If([Date]<=eomonth(today(),0), 1,0)

And user filter flag =1 in the visual level filter

[Anonymous]

@amitchandak 

It is not working for me. As you suggested, I created a flag field like this:

I then added it as a filter for the slicer, but it hasn't changed the appearance of the slicer in any way, as seen below. Am I missing something?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

 If the date field in the slicer is a date hierarchy,please try to change your flag measure as below:

filter Date Hierarchy =
VAR year1 =
    SELECTEDVALUE ( Table1[Date].[Year] )
VAR month1 =
    SELECTEDVALUE ( Table1[Date].[MonthNo] )
VAR day1 =
    SELECTEDVALUE ( Table1[Date].[Day] )
RETURN
    IF ( DATE ( year1, month1, day1 ) <= TODAY (), 1, 0 )

You can also try to create a calendar table and build relationship with your fact table.

Then  you can use the date field to filter data in the slicer.(however,you need to refresh the report to view the latest data)

Calendar=Table = CALENDAR(MIN(Table1[Date],TODAY())

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft This works perfectly. Thank you very much!