How to set dynamic date slicer which shows last two years?

[Quiny_Harl]

Hello,

Is there a way to set a dynamic date slicer which shows last two years?

When the user opens the report in power bi service, the date slicer should be showing last two years.
For example, if they open the report today, the date slicer should be showing the following dates and user would have the option to pick different dates:

This is not achievable by setting a relative date filter on the report. This will filter the report for last 2 years but will not change what the slicer is showing. 

[v-janeyg-msft · Accepted solution]

Hi, @Quiny_Harl 

You can create a meaure,then put it in filter pane to filter past two years.

Like this:

Measure2 =
VAR a =
    YEAR ( TODAY () )
VAR b =
    MONTH ( TODAY () )
VAR c =
    DAY ( TODAY () )
VAR d =
    DATE ( a - 2, b, c ) + 1
RETURN
    IF (
        SELECTEDVALUE ( 'Table 2'[Date] ) >= d
            && SELECTEDVALUE ( 'Table 2'[Date] ) <= TODAY (),
        1,
        0
    )

If you still have problems, please feel free to ask me.

Best Regards

Janey Guo

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

[jdbuchanan71]

It sounds like you will need to add something like a Day_Offset column to your calendar table using 

DATEDIFF ( Dates[Date], TODAY() )

Then you can set that as a filter on the slicer visual.   When the model refreshed that column will recalculate and the slicer should adjust.

[jdbuchanan71]

Did you apply it in the "Filters on this visual" section because it works in my test file..

[Quiny_Harl]

Hi @jdbuchanan71 
Yes, I applied it in "filters on this visual". I had to delete and add the date slicer in order to make it work. Obviously, this feature is not working very well in Pbi.
However, it still doesn't help me because when I upload the report in Power BI service it still "remembers" as a default date the one displyed when the report has been upload and it is not dynamic. 

[jdbuchanan71]

@Quiny_Harl 

If you select the slicer you can see the 'Filters on this visual' section.  There you can set the filter on the date field to be in the last 2 years.

[Quiny_Harl]

Thank you for the suggestion, however, this is still not changing what slicer shows.