Plot showing 2-year evolution using year filter as reference date

[Syndicate_Admin]

Very good!!

I have a dashboard that has a main filter per year, and a graph that shows the evolution of the data in the different years (I have data from 2004 to 2021). My intention is to display the last two years according to the year filter: if the year is marked 2021, it will show from 2019 to 2021, and if 2013 is marked, the data from 2011 to 2013 will be displayed.

I've tried putting a filter by relative date where it shows only the last two calendar years. It would be nice to be limited only to those two years, ignoring the rest, but it would be wrong because it would only show from 2019 to 2021, and also, only the data marked in the main year filter for that particular year would be effective.

Another option was to ignore the main filtering of the year on the graph, but then I can't reference the year to show that data from it.

I hope I have explained my problem well 😅

Would it be possible to display that data in that range dynamically? Thank you very much in advance for the help.

[v-jingzhang · Accepted solution]

Hi @Syndicate_Admin 

You can create a disconnected table which contains all the years in a column. Then create below measures.

Selected Year = SELECTEDVALUE(Years[Year])


Flag = IF(SELECTEDVALUE('Date'[Year])<=[Selected Year]&&SELECTEDVALUE('Date'[Year])>=[Selected Year]-2,1,0)

Put the Year column from Years table into a slicer for users to pick a year. Put the Flag measure into the visuals' filter pane as a visual-level filter and set show items when value is 1 and apply the filter. You need to have a column from Date table in the same visual to provide a context for the measure.

Attached a sample file for your reference.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Syndicate_Admin]

My problem came to the fact that when having the Calendar table related to the data table, when filtering by the year it always showed the data limited to that year, and not with the previous two. If there is no relationship between both tables, then it works perfectly, showing the data defined in that time range set in the measure.

[v-jingzhang]

Yes, that's why we need a new disconnected table to hold year values. The Years table is specially created for the slicer.

[amitchandak]

@Syndicate_Admin , if you select a small period range and want to display a bigger range it is better that you use an independent table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI