Forum Discussion
Rankx & earlier
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.
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.
2 Replies
v-yadongf-msftCommunity Support
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.
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 🙂
