cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamalmsharma Frequent Visitor
Frequent Visitor

How to find last three dates

Hi friends,

 

I have a table for ranking position of runners for races on different dates. I want to create a table to get the ranking position of a runner for his last three races. For that, I need to first find out these three dates i.e. the last date, last but one date and last but two dates. Could you please advise how can I do that? 

3 REPLIES 3
CheenuSing Super Contributor
Super Contributor

Re: How to find last three dates

hi @kamalmsharma

 

Please check out the link

 

https://community.powerbi.com/t5/Desktop/DAX-Ranking-or-Indexing-Dates/td-p/40776

 

This should give you the answer.

 

Using the same I created two calculated columns

 

1. YearMonth =  VALUE (        YEAR ( 'Table1'[Date] ) * 100 +          MONTH ( 'Table1'[Date] )   )

 

2.  RankbyDate =   RANKX (
                                            FILTER (
                                                                'Table1',
                                                                         EARLIER ( Table1[Name] ) = Table1[Name]
                                                           ),
                                          Table1[YearMonth],
                                            ,
                                             DESC,
                                              DENSE
                                            )

 

 

In the martix table,  set Rows as Name, Columns as RankbyDate,  Values as RaceDate, RaceRank.

Set the visual filter for RankByDate is less than 4.

 

Sample output

 

Capture1.JPG

 

 

If it works please accept it as solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
kamalmsharma Frequent Visitor
Frequent Visitor

Re: How to find last three dates

Hi,

 

Thanks for your quick suggestion. Based on that I created 1st column as 'Month&year'. I created 2nd column for ranking using following function:

DateRank = RANKX(filter('Result', EARLIER('Result'[Runner])='Res'[Runner]), 'Result'[Month&year],0)

 

But it is giving same rank for all dates to each runner. Is it because the [Runner] is text column?

 

What can be a solution for this?

 

Regards,

Kamal

CheenuSing Super Contributor
Super Contributor

Re: How to find last three dates

Hi @kamalmsharma

 

Please share your data model and data or the pbix in Google drive / One Drive and the paste the link here to check.

 

 

Cheers

 

CheenuSing 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 83 members 1,610 guests
Please welcome our newest community members: