How to find last three dates

[kamalmsharma]

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? 

[Anonymous]

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

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

Cheers

CheenuSing

[kamalmsharma]

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

[Anonymous]

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