Create a Rank column by using a date column. There might be duplicate dates, but the rank should not

[SriKandimalla]

Hello PBI Comunity,

I am trying to solve the following criteria:

[Latest name] is my target DAX column, which I tried to achieve.
I need to check if a name is duplicate, then check all the dates for that name, and give rank for each row even when the dates are repeated.

I appreciate all the help.
TIA

[Pragati11]

Hi @SriKandimalla.,

Do you have anyother column in your dataset apart from NAME and DATE?

Thanks,

Pragati

[SriKandimalla]

Yes @Pragati11 ,

I have many columns, but I cannot use them. Not relevant to this requirement.

[Pragati11 · Accepted solution]

Hi @SriKandimalla ,

This was a tricky thing to solve but finally got a way to do this by creating an INDEX/ID column to the data.

1. Sample data used is as follows:

2. Created the DAX expression column as follows:

Rank = RANKX(FILTER('Name Rank', 'Name Rank'[Name] = EARLIER('Name Rank'[Name])), 'Name Rank'[Date] - 'Name Rank'[ID] / POWER(10, 9), , ASC, Dense)

3. Then I move it to dashboard as follows:

4. Let's check result by filtering down one person:

We finally get the required result 🙂

If this helps and resolves your issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Pragati11]

Hi @SriKandimalla ,

Just wanted to check, if my latest solutions worked for you? It works in my case. If it works please accept it as a solution ans close the thread.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati