Forum Discussion
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 todays date. So if today is 1/20/22 then it would be to select the date of 12/23/21 and then the other would be a custom date range which would be all dates in my current date calendar. If I clicked the "4 Weeks Prior" option it will change to 12/23/21 in the date slicer and then if I chose "Select a Date" it will allow me to custom chose a date in the slider.
Thank you for any help on this.
Chad
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.
5 Replies
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.
Bookmarks is a great option and you helped me learn about them since this is the first time using them. However in this particular instance I need the 4 Weeks Prior dates to just be 12/23/2021 and not 12/23/2021 - Today. So just 12/23/2021 - 12/23/2021. I like having your option in my toolbox though so thank you for that.
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!One other thing I found in your solution and mine is that when selecting the bookmark the date slicer will move correctly to the dates but then in the table the data doesn't change to reflect what was changed in the slicer. Wonder if that is a bug.
Ok after some testing I was able to just add to that 4 Weeks Prior bookmark the Specific Date column = Yes on the tables and then update the bookmark. All is working now.
