Create a rank/index using created date against ID

[Thomas-B-Hudson]

I have a table where I have multiple TSID's with the same value due to adjustments being made to the original TSID which brings in additional transaction lines.

I would like to identify the original transactions as 0, the first adjustment as 1 and the second adjustment as 2.

Each TSID entry has a different created date so I was hoping to use this to be able to identify which transaction is the original, first adjustment and 2nd adjustment in the example below.

created_at_utc	Timesheet_TSID
07-03-22 21:56	710139
11-10-21 1:38	710139
15-10-21 1:18	710139

So I would like it to look something like this

created_at_utc	Timesheet_TSID	Adjustment
07-03-22 21:56	710139	2
11-10-21 1:38	710139	0
15-10-21 1:18	710139	1

[Ashish_Mathur]

Hi,

Write this calculated column formula

Adjusted = calculate(countrows(Data),filter(Data,Data[created_at_utc]<=earlier(Data[created_at_utc])))-1

Hope this helps.

[danextian]

Hi @Thomas-B-Hudson,

You can create a calculated column that will count the rows that meet a certain criteria (eg <=to a date). Here's a sample:

Rank =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
) - 1
//-1 to make the first one zero

[Thomas-B-Hudson]

Hi @danextian,

This looks to be working in essance, what I am wondering however is I have quite a large dataset which contain these TSID's so you can see from my screenshot below that the values are not 0, 1, 2, etc.

Would you now how to get this formula to reset the count at each new instance of a TSID?

Thanks for your help so far

created_at_utc		Timesheet_TSID		Adjustment
07-03-22 21:56		710139		364813
11-10-21 1:38		710139		308519
15-10-21 1:18		710139		308793

[danextian · Accepted solution]

Hi @Thomas-B-Hudson ,

Try this:

Rank =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && 'Table'[ID] = EARLIER ( 'Table'[ID] )
    )
) - 1
//-1 to make the first one zero

[Thomas-B-Hudson]

Legend!

[mahenkj2]

I would suggest you to refer this link:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

I think you will need to index based on each TSID. If this is not what you are looking for please revert back.