Forum Discussion
Conditional Index Column Based on Date
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
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 )
6 Replies
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
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 )
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,
AngeliaMy 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.
- Hello, did you find a way to create new numbering based on date? I also want to the same but not sure how
