How to sort my year field from most recent 3 year ?

[admin11]

Hi All

YEAR2 = year([Date])

This is what i get now 

May i know how to set , so that it will return :-

Show Last 3 Yr = IF ('DATE'[Year] >= Year(TODAY()) - 3, 1, 0)

Then i get :-

Below is what i expected to get 

May i know any short cut ?

Paul

[Anonymous · Accepted solution]

Hi @admin11 ,

If you want this:

Please manage your table relationships between Year and Year , not Date and Year:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin11]

@Anonymous 

Thank you it work fine now

[Anonymous]

Hi @admin11 ,

Please create a flag measure(not a column):

Measure = IF(MAX('Date'[year])>year(today())-3,1,0)

And drag it to filter pane,set as " is 1":

Or you could create a new table:

New Table = DISTINCT( SUMMARIZE(FILTER('DATE',[YEAR]<=YEAR(TODAY()) && [YEAR]>YEAR(TODAY())-3 ) ,[YEAR]))

Note: when you upload the pbix file, please removing sensitive data firstly

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin11]

@Anonymous 

Thank you very much for your sharing. 

i have try your new table expression.

i manage to get it display year field with 2021 and 2020

I also did the Table link from Date table year with New Table Year field.

But when i create the tick box for new tabe year field , it does not filter only 2020.

I though if i select new table year field value label =2020 , Date Table year field should filter only year=2020.

not sure where i go wrong ?

Below is my PBI file :-

https://www.dropbox.com/s/7cah1xdk8fflz50/new%20table%20for%20date.pbix?dl=0

[admin11]

@VahidDM 

Can you help me take a look as  @Anonymous

may be busy with her work. 

Paul 

[ryan_mayu]

@admin11 

maybe you can create a column and use that column to filter

Show Last 3 Yr = if('Date'[year]>=year(today())-3,1,0)

[admin11]

@VahidDM @ryan_mayu 

Hi All

Thank you for your contribution.

Show Last 3 Yr = if('Date'[year]>=year(today())-3,1,0)

Thank you for give me the above solution . which is i already doing in the past.

Now i like re-phase my question.

I have year field :-

Year = year([Date])

may i know how to add condition , so that it will only display last 3 year value like 2022 , 2021 , 2020

Paul

[VahidDM]

Hi @admin11 

You can create a new table with one column that has 3 years inside with this code, then create a relationship between this new column to the Date[Year] and use the new column in the slicer:

New Table =
VAR _A =
    YEAR ( TODAY () )
VAR _B = _A - 2
RETURN
    CALCULATETABLE (
        VALUES ( 'Date'[year] ),
        FILTER ( ALL ( 'Date' ), 'Date'[year] >= _A && 'Date'[year] <= _B )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[admin11]

@VahidDM 

Thank you very much . I like your suggestion. 

But when i try i get error. 

My PBI sample file :-

https://www.dropbox.com/s/mcky7bql9zitn8c/PBTI_V023.pbix?dl=0

Paul

[ryan_mayu]

@admin11 

the screenshot i provided was selecting 0, if you select 1, then will only show last 3 years' value. you can either use slicer or use visual, page,report filter

[VahidDM]

Hi @admin11 

Can you add a sample of your data with the expected result in a text format?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/