Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count class changes and abandon rate

Hi guys I really don't know how to name this question but here is what im trying to do. I have stores labeled by a class A+, A, B, C and N every time we open a new store we name them N every month i...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

    Please refer to the following measures.

     

    One_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=IF(a<>BLANK(),CALCULATE(MAX([Level]),'Table'[Mon_num]=a+1),CALCULATE(MAX([Level]),'Table'[Mon_num]=MIN('Table'[Mon_num])))
return IF("N" in VALUES('Table'[Level])&&b="C",1,0)

Three_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=IF(a<>BLANK(),CALCULATE(MAX([Level]),'Table'[Mon_num]=a+3),CALCULATE(MAX([Level]),'Table'[Mon_num]=MIN('Table'[Mon_num])+2))
return IF("N" in VALUES('Table'[Level])&&b="C",1,0)
    One_month counts = SUMX(VALUES('Table'[Name]),[One_month change])

three_month counts = SUMX(VALUES('Table'[Name]),[Three_month change])

     

    Output

     

     

    Best Regards!

    Yolo Zhu

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