Customer Status over time

[Anonymous]

Hi,

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:

Current Dataset

Status Logic

Goal

Basically, that is what I want through a formula, to get information about the current client status based on the status change.

Thanks in advance,

IC

[v-xuding-msft]

Hi @Anonymous ,

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.

Best Regards,

Xue

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

[Anonymous]

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]

Hi @Anonymous ,

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,

Xue

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

[Anonymous]

Hi @v-xuding-msft ,

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:

Ultimo Estado =

var mi = MAX('Data clientes lunes por 6 meses'[Rank])

return

CALCULATE(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))

and the final one

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" ))

)

)

Results were the following:

where Ultimo Estado = Last Status

Estado en el tiempo = Current state

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.

This happened to other users but this was the best example I could find. Do you have an idea why this happened?

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.

My question is: Is there a way to only show the useful data?

Also, but not that important, 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?

Thanks in advance,

IC

@v-xuding-msft 

[v-xuding-msft]

Hi @Anonymous ,

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.

1.create a calculated column

Rank = RANKX(FILTER(Table1,Table1[Codigo_Cliente] = EARLIER(Table1[Codigo_Cliente]) && Table1[Codigo_Ingreso] = EARLIER(Table1[Codigo_Ingreso])),Table1[FechaCarga],,ASC,Dense)

2. create measures

Last 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 Ding

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