Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 createThe grey column is what I am trying to create

 

Thank you for your help

 

Herbz

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

Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

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

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

Best Regards,
Angelia

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.

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

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 )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thank you very much, works perfectly

 

Much appreciated

 

Herbz

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.