Add Index based on 2 x Columns

[hackfifi]

Hi - Just need some help to create the INDEX column. It should be a sequence number sorted on Period but resets when Project Changes. Happy to use a calculated column or adding it to the Power Query table. Thanks!! 

Project	Period	Index
A	Jan-20	1
A	Feb-20	2
A	Mar-20	3
A	Apr-20	4
A	May-20	5
B	Mar-20	1
B	Apr-20	2
B	May-20	3
B	Jun-20	4
C	Apr-21	1
C	May-21	2
C	Jun-21	3
C	Jul-21	4

[az38]

hi @hackfifi 

try a RANKX() column

Index = 
    RANKX(
        FILTER(
                'Table',
                'Table'[Project]=EARLIER('Table'[Project])
                ),
        'Table'[Period],,
        ASC
        )

[hackfifi]

Hi @az38  - Thanks for respondng. but it did not work. I filtered on 1 Project, But the index column is giving me random values

Index = RANKX(filter('Table','Table'[Project]=earlier('Table'[Project])),'Table'[Period],,ASC)

[az38]

@hackfifi 

yes, you need to define tie-break rule for the one date - one project rows

[hackfifi]

So the tie break rule is when the project changes, the index resets. So what am i doing wrong? 🙂

[v-deddai1-msft · Accepted solution]

Hi @hackfifi ,

Would you please try to use 

Index = RANKX(filter('Table','Table'[Project]=earlier('Table'[Project])),'Table'[Period],,ASC,Dense)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai