Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi.
I have created a sample table below for reference.
I need to create a line chart. How can I create a measure to only display the last 3 years of records (complete cycles) from the current year?
Thank you in advance.
Regards,
NickzNickz
.
Solved! Go to Solution.
HI @AjithPrasath ,
Based on your input and the available samples, I create the measure below to get the value.
Last 3 Years =
VAR _currentyear = YEAR(TODAY())
VAR _previousyear = SELECTEDVALUE(test_last3years[Year])
RETURN
SWITCH(
TRUE(),
_previousyear <= _currentyear -1 &&
_previousyear >= _currentyear -3,1,
0
)
Regards,
NickzNickz
Hi @NickzNickz ,
create a measure to determine the current year. You can use the TODAY() function to get the current date and extract the year from it
Current Year = YEAR(TODAY())
Now, create a measure that calculates the last 3 years from the current year. Let's name this measure "Last 3 Years"
Last 3 Years = IF([Year] >= [Current Year] - 3 && [Year] <= [Current Year], 1, 0)
Finally, use the "Last 3 Years" measure as a filter in your line chart visual. Set the filter to include only the records where the measure evaluates to 1.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
I have successfully created the measure for Year.
For measure #2, how can I get the column [Year] .... ?
For that, use the year column in your table===> 'Tablename'[year]
If I answered your question, please mark my post as solution, Appreciate your Kudos
HI @AjithPrasath ,
Based on your input and the available samples, I create the measure below to get the value.
Last 3 Years =
VAR _currentyear = YEAR(TODAY())
VAR _previousyear = SELECTEDVALUE(test_last3years[Year])
RETURN
SWITCH(
TRUE(),
_previousyear <= _currentyear -1 &&
_previousyear >= _currentyear -3,1,
0
)
Regards,
NickzNickz
IF you want to use simplified code , you can use the below code :
Last 3 Years =
VAR _currentyear = YEAR(TODAY())
VAR _previousyear = SELECTEDVALUE(test_last3years[Year])
RETURN
IF(_previousyear >= _currentyear - 3 && _previousyear <= _currentyear - 1, 1, 0)
If you got your answer, please mark this as answer and give kudos
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |