Generating a sub index based on original index value and date values

[RichardJ]

Hi,

Please see the attached Power BI file 

https://www.dropbox.com/s/tyybzdbqz84d8mf/sub-index%20question.pbix?dl=0

The sample data shown is in the fileSample Data

and i'm trying to generate a custom formula (or whatever works best) to generate a sub index using the logic shown

Sample Data Index Added

 The logic for incrementing the sub index would be

This is where I am trying to add a counter to increment an index for each change in Date.
e.g

row 1 would have 1 in the Sub Index due to it having the earliest date for the index value of 1
row 2 would have 2 in the sub index due to it having the next date for the index value of 1
row 3 would have 3 in the sub index due to it having the next date for the index value of 1
row 4 would have 1 in the sub index value due to it having the first date for the index value of 2)

I'd like to be able to count the sequence of each file in the order they were created.

Any help appreciated,

Thanks,

Richard

[bcdobbs · Accepted solution]

Have a look at this example.

Few points:

In power query sorted by date column and added a Table.Buffer to ensure the sorting is complete.

Then before you expand your existing group add a custom column:

Table.AddIndexColumn([Count], "SubIndex", 1, 1)

Expand everything and clean up:

[RichardJ]

Thanks very much @bcdobbs for the comprehensive and quick response.

This works perfectly and not an approach I would have thought of.