topic Re: Record only display last 3 years (complete cycles) value in DAX Commands and Tips

Record only display last 3 years (complete cycles) value

NickzNickz — Thu, 08 Jun 2023 07:56:08 GMT

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

Re: Record only display last 3 years (complete cycles) value

AjithPrasath — Thu, 08 Jun 2023 08:15:57 GMT

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 👍

Re: Record only display last 3 years (complete cycles) value

NickzNickz — Thu, 08 Jun 2023 08:43:07 GMT

AjithPrasath ,

I have successfully created the measure for Year.

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

Re: Record only display last 3 years (complete cycles) value

AjithPrasath — Thu, 08 Jun 2023 08:47:44 GMT

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

Re: Record only display last 3 years (complete cycles) value

NickzNickz — Thu, 08 Jun 2023 08:52:18 GMT

hi AjithPrasath ,

I tried but couldn't find the table name.

Re: Record only display last 3 years (complete cycles) value

NickzNickz — Fri, 09 Jun 2023 02:03:50 GMT

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

Re: Record only display last 3 years (complete cycles) value

AjithPrasath — Fri, 09 Jun 2023 04:21:14 GMT

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

Re: Record only display last 3 years (complete cycles) value

NickzNickz — Fri, 09 Jun 2023 09:17:33 GMT

Hi AjithPrasath

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