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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
admin11
Memorable Member
Memorable Member

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

Hi All

 

YEAR2 = year([Date])
This is what i get now 
admin11_1-1642027318057.png

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

 

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

Then i get :-

Below is what i expected to get 

admin11_3-1642027505404.png

May i know any short cut ?

 

Paul

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @admin11 ,

 

If you want this:

filter tables.gif

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

Eyelyn9_0-1642467599030.png

 

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.

View solution in original post

11 REPLIES 11
v-eqin-msft
Community Support
Community Support

Hi @admin11 ,

 

If you want this:

filter tables.gif

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

Eyelyn9_0-1642467599030.png

 

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.

@v-eqin-msft 

Thank you it work fine now

v-eqin-msft
Community Support
Community Support

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":

Eyelyn9_0-1642398323194.png

 

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.

@v-eqin-msft 

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

 

@VahidDM 

Can you help me take a look as  @v-eqin-msft

may be busy with her work. 

Paul 

ryan_mayu
Super User
Super User

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

2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

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/

 

 

@VahidDM 

Thank you very much . I like your suggestion. 

But when i try i get error. 

admin11_0-1642068377019.png

My PBI sample file :-

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

 

Paul

 

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




VahidDM
Super User
Super User

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/

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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