Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Relative date filter not working on Hierarchical slicer

Hi, new user here.

 

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

 

Kit_0-1621563373731.png

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.

 

Kit_1-1621563640433.png

 

Thank you!

1 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 )

45.png

 

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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
Not applicable

@amitchandak 

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

Kit_0-1621580979351.png

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?

Kit_1-1621581710282.png

 

 

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 )

45.png

 

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.