Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NickzNickz
Helper IV
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?

NickzNickz_0-1686210353639.png

 

Thank you in advance.

 

Regards,

NickzNickz

 

.

1 ACCEPTED 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

 

View solution in original post

7 REPLIES 7
AjithPrasath
Resolver II
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.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

@AjithPrasath ,

 

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

 

@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

Hi @AjithPrasath 

 

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

hi @AjithPrasath ,

 

I tried but couldn't find the table name.

 

NickzNickz_0-1686214269002.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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