Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
Did you apply it in the "Filters on this visual" section because it works in my test file..
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.
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.
Thank you for the suggestion, however, this is still not changing what slicer shows.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |