topic Re: Calculate a date sequence on a row level per ID (in DAX) in DAX Commands and Tips

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

fapenar — Tue, 12 Sep 2023 04:53:16 GMT

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!

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

vicky_ — Tue, 12 Sep 2023 05:43:16 GMT

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])))

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

fapenar — Tue, 12 Sep 2023 20:57:42 GMT

It's working perfectly. Thanks, Vicky!