Dynamic Date filter on a Chart

[RichFlorida]

Hello All,

I need to add a filter to my chart to make it display data for current_year only.

I want this filter to be dynamic, this year will be 2022, but next year will automatically change to 2023.

I can’t create new columns as I connect directly to DB.

Any ideas?

Thanks!

[v-yangliu-msft · Accepted solution]

Hi  @RichFlorida ,

I created some data:

2019 1.1 – 2023.12.31

Here are the steps you can follow：

1. Create measure.

Flag =
IF(
    YEAR(MAX('Table'[Date]))=YEAR(TODAY()),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Only data for the current year will be displayed

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jdbuchanan71]

Then you will proabaly have to add a column to your date table, something like FY offset that tags the current year as 0 and the previous year as -1 and the year before as -2, etc.  This would need to be dynamic so when it rolls to 2023, the year offset sets the correct dates as 0 to identify the current year.   Apply the year offset field as a filter on your report and set it to 0.

[RichFlorida]

Thank you @jdbuchanan71 

what is i'm interested in current Fiscal Year which starts in October 1st?

so, up tilll end of Sep it is one FY, starting Oct is is another..!

[jdbuchanan71]

@RichFlorida 

You can put a slicer on the page with the date and set it to relative, then select this year.

When the meaning of 'this year' changes on 1/1/2023, the date range will shift automatically.