Conditional Index Column Based on Date

[Anonymous]

Good day to you all

I am looking for some assistance in creating an index column called Trading Day No. based on the sorted date column. Index numbers should begin at 1 and all rows with the same date should have the same index number as highlighted in the attached. In Excel I am using the formula "=IF(A7=A6,D6,D6+1)". Please see pic below:

The grey column is what I am trying to create

Thank you for your help

Herbz

[v-huizhn-msft]

Hi @Anonymous,

Please use the solution @Zubair_Muhammad posted, it works successfully after test.

expected result
In addition, please mark the right reply as answer, so more members will get useful information or workaround easily.

Best Regards,
Angelia

[vitalijusb_sisp]

My problem is very similar, I need to numbering the records for each day. The numbering for each day starts anew, i.e. from 1. Could you help?

Thanks.

[Aqila_Balqis]

Hello, did you find a way to create new numbering based on date? I also want to the same but not sure how

[jthomson]

Sounds like a job for Power Query - add steps where you:

- group your table by the date column

- sort it if needed

- add an index column

- join this new table back into what you had in your previous step by the date column, your index column should give you the output you wanted

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

With DAX you can achieve the same with RANKX

Add it as a calculated column

Trading Day No. =
RANKX ( TableName, TableName[Date],, ASC, DENSE )

[Anonymous]

Thank you very much, works perfectly

Much appreciated

Herbz