Simple EARLIER function fail. What the heck am I missing?

[charleshale]

I have a simple column of text labeled Key_AR in the table 'Bill1_AR' (tough table name to read, I know but I'm not changing it to Table1 in case that has something to do with the problem).    I am able to count the number of times Bill1_AR[Key_AR] is duplicated***.   What I am struggling to do, however, is use a simple EARLIER function to give each duplicate a sequential number.

I must be missing something basic but I'm not seeing it.   What I am doing is simply:

1. Creating an Index in Power Query to give me something to count against for EARLIER

2. Add Column: 

Column = CALCULATE(count(Bill1_AR[Key_AR]),Bill1_AR[Index]<=EARLIER(Bill1_AR[Index]))

I would expect this "Column" to return the following desired results instead of what it does above, which is only returning 1. 

Key_AR	Count Dupes	Index	Column
INV7695~consumption tax - other~336.83	3	832	1
INV7695~consumption tax - other~336.83	3	833	2
INV7695~consumption tax - other~336.83	3	834	3
INV7695~custom testing~4210.38	3	836	1
INV7695~custom testing~4210.38	3	837	2
INV7695~custom testing~4210.38	3	838	3
INV9374~custom testing~11250	3	1063	1
INV9374~custom testing~11250	3	1064	2
INV9374~custom testing~11250	3	1065	3
INV7694~other~0	2	1804	1
INV7694~other~0	2	1805	2
INV7694~consumption tax - other~0	2	1806	1
INV7694~consumption tax - other~0	2	1807	2
INV5586~custom testing~5836.95	6	2571	1
INV5586~custom testing~5836.95	6	2572	2
INV5586~custom testing~5836.95	6	2573	3
INV5586~custom testing~5836.95	6	2574	4
INV5586~custom testing~5836.95	6	2575	5
INV5586~custom testing~5836.95	6	2576	6
INV5586~custom testing~7004.34	3	2577	1
INV5586~custom testing~7004.34	3	2578	2
INV5586~custom testing~7004.34	3	2579	3
INV6345~custom testing~1416.47	5	2659	1
INV6345~custom testing~1416.47	5	2660	2
INV6345~custom testing~1416.47	5	2661	3
INV6345~custom testing~1416.47	5	2662	4
INV6345~custom testing~1416.47	5	2663	5
INV7273~reprints~136.5	4	2760	1

What am I missing?   I tried COUNTA.   

Vexed! 

Thank you for your suggestions

** code used:

Count Dupes = 
Var CheckCountARKey_AR = 'Bill1_AR'[Key_AR]
RETURN

CALCULATE(
COUNTROWS('Bill1_AR'),
all('Bill1_AR'),
'Bill1_AR'[Key_AR]= CheckCountARKey_AR
)

[jdbuchanan71 · Accepted solution]

Hello @charleshale 

You are getting locked in the row context of the table.  Give this a try.

Column 2 = 
VAR _Index = Bill1_AR[Index]
RETURN
CALCULATE(
    COUNTROWS( Bill1_AR),
        ALLEXCEPT( Bill1_AR,Bill1_AR[Key_AR] ),
        Bill1_AR[Index] < _Index
) +1

I use a VAR instead of EARLIER, I think it makes it easier to read. 

[charleshale]

@jdbuchanan71 - you have unlocked me from the row context.   Thank you.  This works great and is much more elegant.

[parry2k]

@charleshale try this, add as a column

Column 2 = CALCULATE( COUNTROWS( 'Table'  ), ALLEXCEPT( 'Table', 'Table'[Key_AR] ), 'Table'[Index] <= EARLIER( 'Table'[Index] ) )