Create timepoint for observation based on dates

[eHorizons]

Hi Everyone!

I have a table like the one below with IDs that correspond to a specific person and dates of observation. I am wondering how to create the Timepoint column in bold below. I want to label the first, second, third, etc observation for each person based on the date field. Any ideas?

ID	Date	Timepoint
123	1/1/2023	2
546	2/3/2023	1
876	12/13/2022	1
123	5/8/2021	1
876	6/1/2023	2

[DallasBaba]

@eHorizons You can create the Timepoint column in Power Query.

1. Select the ID and Date columns by holding down the Ctrl key and clicking on each column header.
2. Click on the Group By button in the Transform tab.
3. In the Group By dialog box, select ID as the Group By column and click on the Advanced button.
4. In the Advanced dialog box, enter Timepoint as the New column name and select Count Rows as the Operation. You can also enter a custom name for the Count Rows column if you prefer.
5. Click on OK to close the Advanced dialog box and then click on OK again to close the Group By dialog box.
6. The Timepoint column will be added to your table with the first observation for each person labeled as 1, the second observation labeled as 2, and so on.

If you want to label the observations in reverse order (i.e., the most recent observation labeled as 1), you can sort the table by ID and Date in descending order before performing the Group By operation.

NOTE: You can also archive the same result using a DAX Measure:

Timepoint = RANKX(FILTER(ALL('Table'), 'Table'[ID] = EARLIER('Table'[ID]) && 'Table'[Date] <= EARLIER('Table'[Date])), 'Table'[Date], , ASC)

Please click Accept as a solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.

If the content was helpful in other ways, please consider giving it a Thumbs Up.

[AlienSx · Accepted solution]

hi, @eHorizons 

let
    Source = table_like_the_one_below,
    gr = Table.Group(
        Source, "ID", 
        {{"points", each Table.AddIndexColumn(Table.Sort(_, "Date"), "Timepoint", 1, 1)}}
    ),
    expand = Table.ExpandTableColumn(gr, "points", {"Date", "Timepoint"})
in
    expand