Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

how to use date slicer and use it as filter

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Not applicable

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)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.