DAX formula - Cumulative Count

[Vaslo]

Hello,

I am trying to do something like this: I have a column with names of cities:

Richmond
Cleveland
Cleveland
Boston
Cleveland
Boston

I would like a cumulative count down the column in PowerPivot:

Richmond 1
Cleveland 1
Cleveland 2
Boston 1
Cleveland 3
Boston 2

I have tried the following, but it just returns the total count to each matching row:

=CALCULATE (
    COUNTROWS ( CityList ),
    FILTER (
        CityList,
        CityList[CityName]
            = EARLIER ( CityList[CityName]
 )
    )
)

Thanks in advanced!

 

[ralliartur]

First: this will work only in calculated column
Second: it requires index column

=CALCULATE (
    COUNTROWS ( CityList ),
    FILTER (
        CityList,
        CityList[CityName]
            = EARLIER ( CityList[CityName]) && CityList[Index] <= EARLIER(CityList[Index])
 
    )
)

 

[Vaslo]

That did it, many thanks. Don't have a great understanding of the EARLIER formula, and definitely had no idea you could even do a 3 item equality check like that.