Auto Filter Chart based on Selected Year on Slicer

[mclhone]

Hi,

In the below chart, if I select Year (FY) 2020 the chart should only show Jul-19 to Jun-20 and if I select Year (FY) 2021 the chart should only show Jul-20 to Jun-21. All the dates are properly coded base on the FY from Jul-Jun. Currently I have 2 FY's (2020 & 2021) worth of data sets in the source file.

Greatly appreciate any tips and recommendations.

Many thanks in advance.

[Anonymous · Accepted solution]

If you select 2000 and the axis is NOT FILTERED, it means your relationships are non-existent or wrong. Whatever you store in a fact table, you should have a proper calendar and then link the calendar to the fact table on the Date field. No other date characteristics should be stored in a fact table. Then and only then will you get a correct, simple, easy-to-maintain and code-against model.

Best
D

[sturlaws]

Hi @mclhone 

this should be pretty straight forward, if you have a date table with fiscal year in it. What does your model look like?

Cheers,
Sturla

[mclhone]

Thanks @sturlaws  - for having a look at my query.

Below is my simple data model of the chart, with the Month column linked to the overall Calendar table. I used the Calendar as the x-axis reference.

Appreciate the feedback.

Thanks and regards

[sturlaws]

If you add FY to the date table, and create a slicer on FY, I think you should get what you want