how to use date slicer and use it as filter

[Anonymous]

Hello,

I have a slicer which is based on the fiscal Year (field) of a table named 'Calendar'.  Therefore, the slicer shows values, let's say from 1970 until 2025.

As I am interested only having the current year and the year before, I must go into the slicer filter and select only 2018 and 2019.

It works and that fine.

But in the key measure, I am able to define the current year as follow:

CurrentYear = Year(NOW())

The starting year become equal to the currentYear minus one.

I also can define in Key measure the variable StartingYear = CurrentYear - 1. 

However, I don't know how I can use those two variables with a dynamic filter in order to allow the year slicer to show only the current year and the year automatically.  So, in 2020 and after, I won't have to update the information on the filter.  It will do it alone.

Does someone know how to do that&?

Tanks in advance for help

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

In my opinion, you can write calculated column/measure to check date range based on today function.
Then you can drag it to page level filter to control which records displayed in slicer.(slicer not support to add visual level filter)

Sample:

Tag =
IF (
    YEAR ( Table[Date] )
        IN { YEAR ( TODAY () ) - 1, YEAR ( TODAY () ) },
    "Y",
    "N"
)

In addition, you can also try add additional date slicer with 'relative slicer' mode and year field to choose last 2 year records.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello,

I have made some tests with the relative slicers but it did not work with my table.

Here's the solutions I have implemented based on your first solution.

Thanks a lot for your help.

alepage

FlagFiscalYear = SWITCH(true(),

'dim Calendar'[fiscalyear] = 'Key Measures'[StartingYear],1 ,

'dim Calendar'[FiscalYear] = 'Key Measures'[CurrentYear],2 ,

0)