Setting Relative Start and End Dates with Between type Date Slicer

[pritzlb]

So I have a "Between" style date slicer which is working nearly exactly as the users want.  The problem is that they want the start and end values of the slicer to be set dynamically -- the ending date set to yesterday and the starting date to 30 days prior.  So when users open the report it is showing the prior 30 days of data and they can change form there.  I have searched in vain for a way to do this.  I can put filters on the report or page but they do not change the start and end date values in the slicer.  I am manually changing dates and republishing each day.  Ridiculous.

I know I can put in a relative date slicer -- but they users want calendar controls to select a start and end date for the ultimate in flexibility.

[v-frfei-msft]

Hi @pritzlb,

Based on my test, I made an sample for your reference.

You can create a filter column using the formula.

filter = IF(cal[date]<=TODAY()-1 &&cal[date]>=TODAY()-30,cal[date],BLANK())

Then you can get the result as you need.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/g3w9sq2nipf723r/Setting%20Relative%20Start%20and%20End%20Dates%20with%20Be...

Regards,

Frank

[pritzlb · Accepted solution]

Thanks.  This works -- only as long as you do not accidentally select a date from the dropdown calendar (as I might do to test) and then save the report.  Once you select a date from the calendar drpdown in the slicer it ignores the formula and caches the value you selected.  The only solution I have found is to recreate the slicer from scratch and not pick a date.  This is a caveated solution.  Thanks.