cancel
Showing results for 
Search instead for 
Did you mean: 
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
Eyelyn9
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
Eyelyn9
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.

admin11
Memorable Member
Memorable Member

@Eyelyn9 

Thank you it work fine now

Eyelyn9
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.

admin11
Memorable Member
Memorable Member

@Eyelyn9 

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
Memorable Member
Memorable Member

@VahidDM 

Can you help me take a look as  @Eyelyn9

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/

 

 

admin11
Memorable Member
Memorable Member

@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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!