Calculate a date sequence on a row level per ID (in DAX)

[fapenar]

Hi there, 

I have this data set where each patient goes through different treatments (each row is a new treatment).

I would like to add a new column (using DAX) that creates a sequence of numbers from 1 to "N" for each Patient, being 1 assigned to the earliest date. 

This is what the new column should look like:

Patient	Created on (d/mm/yyyy)	Treatment number (calculated column)
Patient A	16/08/2023	3
Patient A	5/05/2023	2
Patient A	25/03/2023	1
Patient B	13/08/2023	2
Patient B	5/05/2023	1
Patient C	19/06/2023	1
Patient D	8/09/2023	2
Patient D	14/07/2023	1

Thanks!

[fapenar]

It's working perfectly. Thanks, Vicky!

[vicky_ · Accepted solution]

Try the following formula in a calculated column:

Treatment Number = COUNTROWS(FILTER('Table', 'Table'[ Created on (d/mm/yyyy)] <= EARLIER('Table'[ Created on (d/mm/yyyy)]) && 'Table'[Patient] = EARLIER('Table'[Patient])))