Forum Discussion
Dynamic year filter
Hi People,
Currently, I create visualisations and yearfilters in PBI desktop.
After uploading those reports to PBI Service I select e.g. the year 2020 and pin a visualisation to an dashboard.
Howerver, when the year changes to e.g. 2021, the visual should be pinned again to the dashboard with the year filter selected on 2021.
Currently I've changed the filter to "Current year", "Prior year", "Two years ago", but the end-user should every time calculate how many years ago a specific year is.
Does somebody know how I can pin a visual to a dashboard by selecting a year with the normal filter?
Hi, jhaast
Yes, you can. You don't need to use slicer. You can use a measure in the filterpane of the visuals you want to pin to the dashboard, then it will change dynamically.
Like this:
Measure = VAR a = YEAR ( TODAY () ) RETURN IF ( VALUES ( 'Table'[Year] ) IN { a, a - 1, a - 2 }, 1, 0 ) //if you use date ,you can try:IF ( Year ( 'Table'[Date] ) IN { a, a - 1, a - 2 }, 1, 0 )If it doesn’t solve your problem, 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.
6 Replies
Hi. You can try relative date slicer. If that year has a date column somewhere, you can do this. If it doesn't, just create it like YEAR-01-01. The relative will let you pick "This Year" considering TODAY date. You can also use last N months/year if that was the case.
Hope that helps
Hi,
That helps, thank you! If I want to select current year and last two years(2019-1-1 -> 2021-02-08) is that also possible with the relative data range slicer?
Mmm.... I don't think so. If you want something that custom you might need to play with a Calendar Table.
The relative will let you filter by last year (from today's date to 365 days before) or last year (calendar) (e.g. filtering completed years, if you put 1 last calendar year would be filtering by 2020)
Hope that make sense
Hi, jhaast
The slicer cannot be pinned to the dashboard, so what is the data you put on the dashboard, measure or column? Can you explain it in detail?
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.
Items which I would pin to the dashboard are:
- A table like this one:
- Or a bar chart like this one:
So currently, 2019, 2020 en 2021 are manually select but when the year changes to 2022 I would see 2020, 2021 and 2022 and I need to change the years manually. It would be great if I could select last two years and current year with a relative date filter.
An solution should be the option to select current year in the filter / slicers below:Like the posibility to select "Today" for the filtering in days.
Could this be possible?
Hi, jhaast
Yes, you can. You don't need to use slicer. You can use a measure in the filterpane of the visuals you want to pin to the dashboard, then it will change dynamically.
Like this:
Measure = VAR a = YEAR ( TODAY () ) RETURN IF ( VALUES ( 'Table'[Year] ) IN { a, a - 1, a - 2 }, 1, 0 ) //if you use date ,you can try:IF ( Year ( 'Table'[Date] ) IN { a, a - 1, a - 2 }, 1, 0 )If it doesn’t solve your problem, 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.
