Forum Discussion
Solved
Custom Date Slicer
I am struggling to figure out this exact custom date slicer. I have a report that needs two selectable options for a date slicer. The first will be to select a date that is 4 weeks prior to today...
You can add a column to your date table that calculates the week offset like this.
Week Offset = - INT ( DIVIDE ( TODAY () - [Date], 7 ) )That will give us a column that is updated every time the model is refreshed so the days that are marked with week offset 0 to week offset -3 will shift as TODAY() shifts.
Then you set up two bookmarks that change your date slicer only. One is wide open, "Select a date". The other applies a filter to the slicer where the week offset is <= 0 and >= -3.
Then attach the bookmarks to a bookmark navigation button.
I have attached my sample file for you to look at.
My workaround for this was to create another column in my DateTable with the following.
Specific Date =
SWITCH(TRUE(),
AND(DATEDIFF(DateTable[Date],TODAY(),DAY)>=0,
DATEDIFF(DateTable[Date],TODAY(),DAY)=28), "YES")
This got me to the correct date. I can then, using the bookmarks, change that 4 Weeks Prior filtering on that Specific Date column = Yes.
There might be a better solution but this appears to be working. If you can think of another way let me know and again thank you for the help!