Dynamic ranking based on date

[saranee]

Hi all,

Request your help on this.

We are basically having a table with Name,Date and based on date we are assigning Rank.

We have calculated column to calculate rank:RANKX(FILTER(Table1,Table1[Name]=EARLIER(Table1[Name])),Table1[Date],,DESC,Dense)

But when we are changing date filter i.e going to back date rank is not getting updated accordingly.For e.g for A when we changed date filter from 15 feb to 8 Feb then A on 3rd Feb will be having rank as 1 but still it remains 2.

Please can we have a solution for this.

Thanks,
saranee

[Anonymous]

Hello,

I want to create a new column ranking on the basis of certain values in a column (AHT). However, I want the ranking to be filtered according to the Date. I mean it should rank the values in AHT, if I filter the date to Jan-22 then it should give me ranking on the basis of date and not for overall dates.

Ecode	Ename	AHT	Date
45654	A	382	Jan-22
45655	B	289	Jan-22
45654	A	351	Feb-22

[Ashish_Mathur]

Hi,

Download my PBI file from here.  Let me know if this is working well.

[Ashish_Mathur]

Hi,

Try this measures

Date value=SUM(Table1[Date])

Rank=RANKX(ALL(Table1[Date]),[Date value])

If this does not help, then share the link from where i can download your file.

[ricardocamargos]

Hi @saranee,

Why don't u use measure instead of calculated column ?

Ricardo

[saranee]

HI Richard,

We will be then unable to use EARLIER in measure.Please can you suggest some alternative as we want seperate set of names and then trying to find rank.

Thanks,

saranee

[ricardocamargos]

Hi @saranee,

Try this code:

_Index =
IF(ISBLANK(CALCULATE(COUNTROWS(Table2); FILTER(ALLSELECTED(Table2[Date]); Table2[Date] < MAX(Table2[Date]))));
1;
CALCULATE(COUNTROWS(Table2); FILTER(ALLEXCEPT(Table2; Table2[Name]); Table2[Date] < MAX(Table2[Date]))) + 1)

Ricardo

[Zubair_Muhammad · Accepted solution]

@saranee

Try this MEASURE for dynamic RANKING

RANK =
RANKX (
    FILTER ( ALLSELECTED ( Table1 ), Table1[Name] = SELECTEDVALUE ( Table1[Name] ) ),
    CALCULATE ( SELECTEDVALUE ( Table1[Date] ) ),
    ,
    DESC,
    DENSE
)

[Hitesh-Dsouza]

Kudos on this solution

[Anonymous]

@Zubair_Muhammad Thanks for the solution, it worked seamlessly. 

[rishikdutta1987]

@Zubair_Muhammad Thats the correct solution!!

[v-huizhn-msft]

Hi @rishikdutta1987,

Please mark the right solution as answer, so more people like you can find the solution easily.

Best Regards,
Angelia