Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |