Rank date in PowerBI

[tomwolswijk]

Hi,

I'm looking for a DAX formule to rank the date kolomn. No extra filters on Client ect, just rank the date.

Every month new data is loaded in the PowerBI model. Now i want to add a Kolomn so that 1 = newest month, 2 = month before, ect. Can someone help me on this?

Table:

Month	Client	Amount
jan-20	1	100
feb-20	1	122
feb-20	1	1
mar-20	1	140

Outcome:

Month	Client	Amount	RANK
jan-20	1	100	3
feb-20	1	122	2
feb-20	1	1	2
mar-20	1	140	1

[Marlow]

thanks

[v-stephen-msft · Accepted solution]

Hello @tomwolswijk ,

You can test this calculated column.

Rank = 
RANKX (
    'Table',
    RANKX ( 'Table', YEAR ( 'Table'[Date] ),, DESC, DENSE ) * 100
        + RANKX ( 'Table', MONTH ( 'Table'[Date] ),, DESC, DENSE ),
    ,
    ASC,
    DENSE
)

More details can be found here.

Best regards

Stephen Tao

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[tomwolswijk]

Thanks a lot! It worked! 

I used 'Table'[Month] instead of 'Table'[Date], because month is already a date data type. Thanks!

[amitchandak]

@tomwolswijk ,

you can use all except or sub-category  for month

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[tomwolswijk]

I tried; 

Rank =

RANKX( (

    ALLSELECTED ( Table[Month] ),

    CALCULATE ( SELECTEDVALUE ( Table[Month] ) ),

    ,

    ASC

))

But did not work... Can you help?

[amitchandak]

@tomwolswijk , I created a measure and then used Rank

Measure = CALCULATE(SUM('Table (2)'[Amount]) ,ALLEXCEPT('Table (2)','Table (2)'[Month]))
Measure 2 = rankx(ALL('Table (2)'[Month]),[Measure])

[tomwolswijk]

The measure you gave is to rank [Amount]. But i'm only want to give my date value a rank based on the date, without using other variables.

To make it simple:

Month	Rank
01-12-2019	5
01-01-2020	4
01-02-2020	3
01-04-2020	2
01-06-2020	1

Thanks for you help