cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper IV

## Record only display last 3 years (complete cycles) value

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?

Regards,

NickzNickz

.

1 ACCEPTED SOLUTION
Helper IV

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

7 REPLIES 7
Resolver II

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.

Helper IV

I have successfully created the measure for Year.

For measure #2, how can I get the column [Year] .... ?

Resolver II

For that, use the year column in your table===>  'Tablename'[year]

Helper IV

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

Resolver II

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)``````

Helper IV

Thank you for your help and ideas ... Really appreciate that ...

Helper IV

I tried but couldn't find the table name.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors