Dynamic Date Slicer - show all years but default selection to last 3 years

[andy808]

Is there a way to have a slicer show all the years but be able to pre-select (default) to the last 3 years? These last 3 years would need to be dynamic - ie. this year 2024, 2023, 2022 are preselected and next year 2025, 2024, 2023 are preselected.

A requirement we use on all pages is a Clear All Filter Bookmark which I would need this Date slicer to reposnd to as well. Right now I pre-select 2024, 23, 22 and have saved this as the Clear All Filters Bookmark so the page will always returns back to these years.

I have a Calendar Auto Date table with Date[Date] column relationship with the FactTable(FiscalYearDate] column. Both are Date data types.

[v-jiewu-msft]

Hi @andy808 ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table. Use the calender to create the table.

FactTable = CALENDAR(DATE(2020,1,1),DATE(2024,2,27))

2.Click "New column" in the table to create a calculated column, it is used to define what rows are the last three years and other are just show the Date type.

Fiscal year Date = 
VAR _max_date = MAX ('Table'[Date])
VAR _last_three_year = YEAR(_max_date) - 2

RETURN
IF (
    [Date].[Year] >= _last_three_year, "Last 3 years",
    FORMAT ( [Date], "yyyy" )
)

3.Drag the year date column into the slicer visual.

Best Regards,

Wisdom Wu

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

[andy808 · Accepted solution]

I ended up just doing this:

CustomDates = IF('Date'[IsCurrentFY] = [CurrentFiscalYear], [CurrentFiscalYear], IF('Date'[IsCurrentFY] = [CurrentFiscalYear] - 1, [CurrentFiscalYear] - 1, IF('Date'[IsCurrentFY] = [CurrentFiscalYear] -2, [CurrentFiscalYear] -2, 'Date'[IsCurrentFY)))

Thank you for your help 🙂

[andy808]

Thanks for your resonse. I have a Calendar auto table so good there. Last 3 years is an option but the requirement is for the user to choose any year individual but have the last 3 years 2024, 2023, 2022 all selected as default. My thought is to create a calculated column where:

1 - 2024 = "Current FY"   

2 - 2023 = "Last FY"    essentially current FY - 1

3 - 2022 = "3rd FY"     essentially current FY - 2

Do you know the dax for this calculated column? We have a FY July-June

[lbendlin]

Remove the slicer and instead use the Filter Pane. It has Relative Dates features, and users can change the filters as needed.

[andy808]

This is one possible solution, thank you. Our current reports do not have the filter pane visible to users, but it an option.