How to display the last 5 years dependent on year selected in a slicer?

[JT_MSUK]

I have a calendar table set up in a heirachy (Date, Month, Year, Day).

I've got a slicer with the CalenderTable[Year] on it, i'd like to be able to display the last 5 years on my graph dependent on the year selected in the slicer (i.e if it's 2023, it'll show back to 2019, and if my slicer is 2019 it'll show back to 2015).

My current measure works but only picks up the last 5 years from todays date.

Last 5 Years = IF(SELECTEDVALUE(CalendarTable[Date].[Year]) >= YEAR(TODAY()) - 4,1,BLANK())

[JT_MSUK · Accepted solution]

Appreciate the help guys, however I have now got this to work with a disconnected table:

Last 5 Years =

VAR YearSelection = SELECTEDVALUE('CalendarTable Disconnected'[Date].[Year])

Return

IF(AND(MAX(CalendarTable[Date].[Year])<=YearSelection,MAX(CalendarTable[Date].[Year])>=YearSelection-4),1,0)

[NikhilChenna]

Hi @JT_MSUK ,

Try this once, not sure but just give it a try,

Create a calculated column for Year. for eg, YEAR(CalendarTable[Date]) use this in your slicer

create the below measure

Last 5 Years = IF( 'CalendarTable'[Year] >= SELECTEDVALUE('CalendarTable'[YEAR]) - 4,1,BLANK())

Regards,

Nikhil Chenna

Please give a thumbs up if it solves your problem

[Ahmedx]

and pls try this

5 year  = 
VAR _LastYear = YEAR(TODAY()) -4
RETURN
 IF(SELECTEDVALUE(CalendarTable[Date].[Date]) >= DATE(_LastYear,1,1),1,BLANK()) 

[JT_MSUK]

Unfortunately neither of these seem to work either, the graph doesn't display anything. 😞

Is that something to do with the TODAY bit? 

[Ahmedx]

Последние 5 лет = 
 IF(SELECTEDVALUE(CalendarTable[Date].[Date]) >= DATE(YEAR(TODAY()) - 4,1,1),1,BLANK()) 

[Ahmedx]

pls try this

Last 5 Years = 
IF(SELECTEDVALUE(CalendarTable[Date].[Date]) >= DATE(YEAR(TODAY()) - 4,1,1),1,BLANK()) 

[JT_MSUK]

Unfortunately doesn't work either, the graph is blank. I have set this value to 1 in the filter.

[Ahmedx]

Share sample pbix file to help you.