Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Appreciate the help guys, however I have now got this to work with a disconnected table:
Appreciate the help guys, however I have now got this to work with a disconnected table:
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
and pls try this
5 year =
VAR _LastYear = YEAR(TODAY()) -4
RETURN
IF(SELECTEDVALUE(CalendarTable[Date].[Date]) >= DATE(_LastYear,1,1),1,BLANK())
Unfortunately neither of these seem to work either, the graph doesn't display anything. 😞
Is that something to do with the TODAY bit?
Последние 5 лет = IF(SELECTEDVALUE(CalendarTable[Date].[Date]) >= DATE(YEAR(TODAY()) - 4,1,1),1,BLANK())
pls try this
Last 5 Years =
IF(SELECTEDVALUE(CalendarTable[Date].[Date]) >= DATE(YEAR(TODAY()) - 4,1,1),1,BLANK())
Unfortunately doesn't work either, the graph is blank. I have set this value to 1 in the filter.
Share sample pbix file to help you.
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |