Page level Year filter to always return current year and last 2 years on a Clustered Column Chart

[Eliezer20101980]

Hello Experts,
I am trying to create a page level filter to have the visuals show only 3 years and this should be dynamically done so that when a new year is added to the dataset , it will be the most recent and then the 2 previous year show as well.

e.g 2019, 2020,2021
when new year is added then it should show
2020,2021,2022.

I have attached the PBIX File and the Excel file.

I would appreciate your help in finding the right solution to achieve this.

Regards.

CSM PBIX Latest (1).pbix (135.4 KB) CSMetrics work data.xlsx (12.5 KB)

[Greg]

Hi @Eliezer20101980. If your date table has year offsets, use them to choose your 3-year set (e.g., YearOffset >= -2 AND YearOffset <= 0). If you’re using @Melissa’s excellent extended date table, you’re all set; if not, here’s the link:

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

Hope this helps.
Greg

[Eliezer20101980]

Thank you @Greg.
Yes I have the @Melissa’s excellent extended date table and I have used the YearOffset as you shown in your response.

I am getting 3 years but not the most recent Year. I have 2019,2020,2021, 2022 loaded but when I use the YearOffset to get most recent 3year , I am still getting 2019, 2020, 2021.
please see attached PBIX File.

Thank you.

CSM PBIX Latest (1).pbix (135.2 KB)

[Melissa]

Hi @Eliezer20101980,

@Eliezer20101980

I am getting 3 years but not the most recent Year. I have 2019,2020,2021, 2022 loaded but when I use the YearOffset to get most recent 3year , I am still getting 2019, 2020, 2021.

YearOffsets have a relationship with the current date, so “today” is a date in the year 2021 therefore the YearOffset value for 2021 = 0

A page level filter >= -2 and <= 0 returns 2019, 2020 and 2021 as expected.

If you mean by most recent year the max year in your date table then you’ll have to adjust your page level filters accordingly >= -1 and <= 1

I hope this is helpful

[Eliezer20101980]

Thank you again @Greg. This very helpful and you just solved the problem for me.

I appreciate it.

[Eliezer20101980]

@Greg,

I forgot to ask if there is a way for this to work dynamically without having to make the change on the filter once the file is published?

Thanks

[Keith]

good job @Melissa

[Eliezer20101980]

Thank you @Melissa . My apologies for not Acknowledging you earlier. I was exited about the solution.

I appreciate your great input and solution.

Regards.

[Greg]

Hi @Eliezer20101980. The solution is already dynamic. Each time the report (or [Dates] table) is refreshed the [Dates] table will be recalculated (including the YearOffsets).
Greg

[Eliezer20101980]

Hello @Greg . Thanks for clarifying that.