Forum Discussion
Count class changes and abandon rate
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.
Hi Anonymous
I don't quite understand how to define the first month of store opening, at first I understood that the first month after a certain store is marked N is the month in which the store is open, but you said this Aaron store, then his first month should be January?
Refer to the above understanding, you can refer to the following two 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 b
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 b
I add a number field [Month_num in the sample data you provided
Best Regards!
Yolo Zhu
Hi Anonymous
The thing with Aaron store lets say the data sheet I showed is from the year 2022 but Aaron started working with us in 2021 thats why he starts the year with a classification different from N, in 2022 with the data sheet we know. Chucho started in february thats why he has the N class in february and nothing in january, what I want to do Its like you did count the steps like chucho and the others but ignoring cases like Aaron that never started as N in the year so my table only shows this cases and those jumps.
Here is the example lets say Aaron started january as C class instead of A
Now the formula takes january as the first month and adds it to chucho and the others which is wrong, I dont want it to take this case in consideration because Aaron was never in the N class I just want chucho and the others that joined this year working with us. If they never were in the N class in the year just ignore them "Aaron" and just take into consideration the ones that were N in the year "Chucho" "Juanito"
Thanks a lot for your time Yolo Zhu I really appreciated it!
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.