Forum Discussion
Relative Year Measure Not Affecting Slicer Filter
I have a date table, with dates from Jan-1-2015 through 12-31-2099. From there, I have a "Relative Year" column that calculates:
i.e. the Relative Year value of -1, is the year 2018. This column works across the rest of my workbook, however, when I try and apply it to a slicer's visual-level filter, it doesn't work.
The slicer is taking all years from my date tabe (2015-2099).
My goal is to filter on -4 to 0, so when the user clicks the slicer's drop down, they get 2015-2019, not 2015-2099.
Note: I can drop in the year field from Date[date] and select 2015-2019, but am trying to be dynamic and have less updates to do at the start of next year.
Thanks in advance!
Anonymous , Unfortunatley, I'm not able to drop a file into a cloud source (Co. restriction). However, in trying to create the sample file (I suppose I owe you a thanks for that request :-) I uncovered the problem and a solution.
When I use the original date[date] column, previously mentinoed, I have a line for every day between 1.1.15 and 12.31.99. If I use date[date] for my slicer, I can use the relative year column as a filter and everything works. However, the user would see every single day of every year between 2015 and 2019, not ideal. When I drop the Date Hierarchy "Year" (That automatically shows in the Field pane), the Relative Year filter doesn't work.
The fix was to create a Month and Year column in my date table, and then use the new Year column for my slicer. Using the new Year column, along with the relative year filter got me to my solution.
Now, my date table looks like the following:
My Slicer uses the "Year" column, and is filtered via the "Relative Year" column. Works like a charm!
2 Replies
fischelr01 - It is a very good idea to use relative years. What you are trying to do should work - can you share a sample pbix that is not working?
Anonymous , Unfortunatley, I'm not able to drop a file into a cloud source (Co. restriction). However, in trying to create the sample file (I suppose I owe you a thanks for that request :-) I uncovered the problem and a solution.
When I use the original date[date] column, previously mentinoed, I have a line for every day between 1.1.15 and 12.31.99. If I use date[date] for my slicer, I can use the relative year column as a filter and everything works. However, the user would see every single day of every year between 2015 and 2019, not ideal. When I drop the Date Hierarchy "Year" (That automatically shows in the Field pane), the Relative Year filter doesn't work.
The fix was to create a Month and Year column in my date table, and then use the new Year column for my slicer. Using the new Year column, along with the relative year filter got me to my solution.
Now, my date table looks like the following:
My Slicer uses the "Year" column, and is filtered via the "Relative Year" column. Works like a charm!
