Populate date slicer value for last 1 year excluding today and yesterday

[abhishekjangid]

Hi all,

I create a relative date slicer in which I can set values like "Last x years". But I want to exclude today and yesterday (today and yesterday date is not fixed as we go ahead in time) from this slicer.

In above slicer, I want date from 09-10-2021 to 09-07-2022. And it should be relative as we go forward in time.

How can I really do that? 

Thanks in advance

[v-yangliu-msft · Accepted solution]

Hi  @abhishekjangid ,

You can consider using Betwwen Slicer, use the IF function to filter the date, display the custom start date and end date

Here are the steps you can follow：

1. Create measure.

Flag =
var _today=TODAY()
return
IF(
    MAX('Table'[Date]) >DATE(YEAR(_today)-1,MONTH(_today),DAY(_today)) &&MAX('Table'[Date]) <=_today -2,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[truptis]

Hi @abhishekjangid ,
You can try this:

@abhishekjangid -> if this helps you then please hit the thumbs up & mark it as a solution. Thanks.

[abhishekjangid]

@truptis This does not exclude today and yesterday.

[PowerUserR]

Hi,

I suppose that you can make a new column based on the dates column as this:
New Date=IF([Date]=Today() || [Date] = Today() -1 , BLANK(), [Date])
then use this to filter the visual

As brought up in this topic: https://community.powerbi.com/t5/Desktop/Relative-Date-Slicer-to-Exclude-Today/m-p/2455896#M876884

[abhishekjangid]

@PowerUserR One problem though. I want to set this value as initial value when power bi page loads. But I want user to feel free and change the slicer to include yesterday and today if he wants.

For example: when page loads, slicer value will be from 09-10-2021 to 09-07-2022. But user can change it to 09-10-2021 to 09-09-2022 if he wants.

[truptis]

Hi @abhishekjangid ,
Try this:
create another column in your calendar table:

Column = 'Table'[Date]-2

& now use this new created column -  "Column" in the slicer and put the relative year filter and set it as in the last 1 year.

@abhishekjangid -> Please hit the thumbs up & mark it as a solution if it helps you. Thanks.

[abhishekjangid]

@truptis This does not solve my problem still. If I put filter in Filters Pane then user won;t be able to change those in published report.

I want user to be able to change date range using slicer but at the start date range should be last 1 year excluding today and yesterday.