Create a Index column based on the oldest and latest date in a datetime column

[Ishaq]

Hi,  I have given an own example similar to the dataset that I have. I need to create an index column in a table view which shows the username who has been activated first as 1 and second as 2 and so on. The username who has been activated last should have the latest index number and this calculation should be dynamic in the sense that whenever I refresh(when the data is added), the calculation should give the highest index number to the last person who has been activated last. The 'Activation Date' is a datetime column.  I have the 'Activated Date' filter on the side.

How the ans should look like.

Kindly guide me.

[MAwwad · Accepted solution]

To create an index column based on the activation date in a datetime column, you can follow these steps:

1. Select the table that contains the 'Activation Date' column.

2. Go to the 'Add Column' tab in the ribbon and click on 'Index Column'.

3. Choose 'Custom' under the 'Index' dropdown menu.

4. In the 'Custom' dialog box, enter the following formula:

   =RANKX(ALL('Table'),'Table'[Activation Date],,ASC,DENSE)

   This formula uses the RANKX function to calculate the rank of each record based on the activation date in ascending order. The ALL function removes any filters that may be applied to the table, ensuring that all records are considered. The DENSE parameter ensures that the ranking is continuous, meaning that if two records have the same activation date, they will have the same rank, and the next rank will be skipped.

5. Click 'OK' to create the index column.

Once the index column is created, it will automatically update whenever new data is added or when the 'Activation Date' column is updated. You can also use the 'Activated Date' filter to filter the data based on a specific date range, and the index column will adjust accordingly.

[Ishaq]

Hi, Data slayer,

Thank you for your reply and solution. It has helped a lot.

For the past 2 days, I am facing this issue, certain rank numbers are missing altogther even though there are no same activation (registration date) .

For all null registration dates, it is taking the rank as 1, which is ok, but rank numbers 2,3,5,6,7,25 in the snapshot are missing altogether. ( In the report a lot of ranks are missing as well). is there any plausible explanation and solution for this