Ranking based on Datetime reseting per each Key

Hi, I need to create a ranking, that evaluates the Datetime column and reset for each Key: It should be based on "History New Value Start" and reset back to 1 when there's a new Key. If the first r...

Need Help

v-kelly-msft

4 years ago

Hi vojtechsima ,

Create a column as below:

_Ranking =
VAR _previousField =
    CALCULATETABLE (
        VALUES ( 'Table'[History Field] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) )
    )
VAR _index =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[History New Value] = "new"
                && 'Table'[Index] < EARLIER ( 'Table'[Index] )
        )
    )
VAR _count2 =
    CALCULATE (
        COUNT ( 'Table'[History Field] ),
        FILTER (
            'Table',
            'Table'[Index] >= _index
                && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && 'Table'[History Field] = "status"
        )
    )
RETURN
    IF (
        NOT ( "status" IN _previousField ),
        IF ( 'Table'[History Field] <> "status", 0, 1 ),
        IF ( 'Table'[History New Value] = "new", 1, _count2 )
    )

And you will see: