Rankx & earlier

[ishanjain]

I'm trying to build a calculated column called days_counter using rankx to find out how long the column tcc_sum was negative (in order of snapshot timestamp).

To my knowledge, I'm using the correct DAX but it does not reset when it sees "Neg" on 12/5 (the days counter should be 1 instead of 3 in the last row). This is the formula I'm using, pls help me.

Days_counter = RANKX (

    FILTER (

        'Capacity Change Hist',

        EARLIER ( 'Capacity Change Hist'[SERVICE_AREA] ) = 'Capacity Change Hist'[SERVICE_AREA]

        && EARLIER('Capacity Change Hist'[defc_neg_pos_]) = 'Capacity Change Hist'[defc_neg_pos_]

    ),

    'Capacity Change Hist'[Snapshot Timestamp],

    ,

    ASC

    ,Dense)

[v-yadongf-msft · Accepted solution]

Hi @ishanjain ,

Add an index column in Power Query or in your data source.

Create two columns:

Appear continuously = CALCULATE(MAX('Table'[Index])+1,ALL('Table'),'Table'[defc_neg_pos_] <> EARLIER('Table'[defc_neg_pos_]),'Table'[Index] <= EARLIER('Table'[Index]))

Rank = RANKX(FILTER('Table','Table'[SERVICE_AREA] = EARLIER('Table'[SERVICE_AREA]) && 'Table'[Appear continuously] = EARLIER('Table'[Appear continuously])),'Table'[Snapshot Timestamp],,ASC,Dense)

I think this is the result you want:

Best regards,

Yadong Fang

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

[ishanjain]

@v-yadongf-msft I figured it out! I had to change ALL(table) to ALLEXCEPT(Table[service area]) in "Appear continously because my table had a lot of areas and it was not working dynamically for one single area and that worked. Thanks a ton, you saved me 🙂