Customer Status over time

Question

Hi,&nbsp;I need to track Customer Status change over time. I have a column named Es_Client where it is either S or N. I have tried using calculate(....,dateadd(...,-1,month)) but it doesnt work. Here are some pictures of my data (3 dates per customer / 4 customers in total) and how I would like the results:&nbsp;Current Dataset&nbsp;Status Logic&nbsp;GoalBasically, that is what I want through a formula, to get information about the current client status based on the status change.&nbsp;&nbsp;Thanks in advance,&nbsp;IC

v-xuding-msft · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;Based on my test, you can rank the date firstly, then get the revious value to compary. The following is my sample you can reference to modify yours. And in my sample , I calculated the correct result if there is a previous date for the records.&nbsp;1.create a calculated columnRank = RANKX(FILTER(Table1,Table1[Codigo_Cliente] = EARLIER(Table1[Codigo_Cliente]) &amp;&amp; Table1[Codigo_Ingreso] = EARLIER(Table1[Codigo_Ingreso])),Table1[FechaCarga],,ASC,Dense)2. create measuresLast status = 
var mi = MAX(Table1[Rank])
return
CALCULATE(MAX(Table1[Es_Cliente]),FILTER(ALLEXCEPT(Table1,Table1[Codigo_Cliente]),Table1[Rank] = mi - 1))

Status to date = 
IF(MAX(Table1[Rank]) - 1 = 0 ,BLANK(),IF([Last status] = "S" ,IF(MAX(Table1[Es_Cliente]) = "N" , "Desertion", "Active"), IF(MAX(Table1[Es_Cliente]) = "N" ,"Inactive", "Reactivated")))Best Regards,Xue DingIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

v-xuding-msft · Answer

Hi Anonymous&nbsp;,Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.&nbsp;Best Regards,XueIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

That was a brilliant workaround. I just need to add:

If the last status was blank and the present is 'S' to write "New activation"

If the last status was blank and the current is "N" to write "Prospecto".

If the last status was "N" and the current is blank to write "No Information".

If the last status was 'S' and the current is blank to write "No Information".

Could you add it?

Thanks in advance,

IC

v-xuding-msft · Answer

Hi&nbsp;Anonymous&nbsp;,I modified the fomula you can have a try.Status to date 1 = 
var a = MAX ( Table1[Es_Cliente] )
return 
IF (
    [Last status] = BLANK(),
    IF(a = "S","New activation",IF(a = "N","Prospecto","No Information")),
    IF (
        [Last status] = "S",
        IF ( a = "N", "Desertion",IF(a = "S", "Active","No Information" )),
        IF ( a = "N", "Inactive", IF(a = "S", "Reactivated", "No Information" ))
    )
)Best Regards,XueIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

Hi&nbsp;v-xuding-msft&nbsp;,&nbsp;I was able to get the measure, however, It doesnt work properly. Some of the results make no sense. To refresh your mind, here is the formula:&nbsp;Ultimo Estado = var mi = MAX('Data clientes lunes por 6 meses'[Rank])returnCALCULATE(MAX('Data clientes lunes por 6 meses'[Es_Cliente]),FILTER(ALLEXCEPT('Data clientes lunes por 6 meses','Data clientes lunes por 6 meses'[Codigo_Cliente]),'Data clientes lunes por 6 meses'[Rank] = mi - 1))&nbsp;and the final one&nbsp;Estado en el tiempo = var a = MAX ( 'Data clientes lunes por 6 meses'[Es_Cliente] )return IF ([Ultimo Estado] = BLANK(),IF(a = "S","New activation",IF(a = "N","Prospecto","Prospecto")),IF ([Ultimo Estado] = "S",IF ( a = "N", "Desertion",IF(a = "S", "Active","No Information" )),IF ( a = "N", "Inactive", IF(a = "S", "Reactivated", "No Information" ))))&nbsp;Results were the following:where Ultimo Estado = Last StatusEstado en el tiempo = Current state&nbsp;Errors:2/18/19: Ultimo Estado = S and we can see that the date before 2/11/19 had N as Es_Cliente.6/24/19: Ultimo Estado = Blank and we can see that the date beofre was S.&nbsp;This happened to other users but this was the best example I could find. Do you have an idea why this happened?&nbsp;Also, I got another problem. My date variable "FechaCarga" has info only from each monday of every week. That being said, since we created a measure for all the blanks it uses all the dates and shows "No Information" although FechaCarga doesnt even has those dates.&nbsp;My question is: Is there a way to only show the useful data?&nbsp;Also, but not that important,&nbsp;I tried to use a line chart where my Legend was the measure and I was not able to use it, I could only use it as a tooltip. Is there any way that this measure could work for line graphs?&nbsp;&nbsp;Thanks in advance,&nbsp;IC&nbsp;v-xuding-msft&nbsp;

Forum Discussion

Customer Status over time

5 Replies

Related content

Customer Scorecard

Customer Status

Custom Visual

Fabric Service Status

Customize subtotal in matrix