cancel
Showing results for
Did you mean:
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
Super Contributor

## Re: How to find last three dates

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

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

Proud to be a Datanaut!
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

Super Contributor

## Re: How to find last three dates

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!

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 83 members 1,610 guests
Recent signins: