Group by id, category and rank by date

[bml123]

Hi,

I have data like this and need rank column like this, group by id , category and ordered by date. How do I achieve this?

Id	Date	Amount	Category	Rank
1	01/01/2022	10	A	1
1	04/01/2022	15	A	2
1	28/01/2022	10	A	3
1	17/02/2022	20	B	1
1	25/03/2022	15	B	2
2	02/03/2022	10	A	1
2	19/03/2022	12	A	2
3	05/04/2022	50	A	1
4	29/05/2022	60	B	1

[v-jayw-msft · Accepted solution]

Hi @bml123 ,

Please check the formula:

rankk =
RANKX (
    FILTER (
        'Table',
        'Table'[Id] = EARLIER ( 'Table'[Id] )
            && 'Table'[Category] = EARLIER ( 'Table'[Category] )
    ),
    'Table'[Date],
    ,
    ASC
)

Best Regards,

Jay

[HotChilli]

create another variable (similar to the first one but it will be on Category).

Add a condition to the FILTER to use the new variable (just like the existing one and use &&)  like 

Table[Id] = _id && Table[Cat....

Give it a go and I will help if you are stuck.

[HotChilli]

Something like this?

ColumnNew = var _id = TableV[Id]
RETURN
RANKX(FILTER(TableV, TableV[Id] = _id), TableV[Date],, ASC)

[bml123]

Hi @HotChilli ,

Thanks for this.  I also need to do group by by the category too.  I have edited and posted sample data for that. How do I add that condition in your formula?