cancel
Showing results for
Did you mean:
Member

## Customer Status over time

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.

IC

5 REPLIES 5
Community Support Team

## Re: Customer Status over time

Hi @icespedes ,

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.

Community Support Team

## Re: Customer Status over time

Hi @icespedes ,

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.

Member

## Re: Customer Status over time

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

IC

Community Support Team

## Re: Customer Status over time

Hi @icespedes ,

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.

Highlighted
Member

## Re: Customer Status over time

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:

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

var a = MAX ( 'Data clientes lunes por 6 meses'[Es_Cliente] )
return
IF (
IF(a = "S","New activation",IF(a = "N","Prospecto","Prospecto")),
IF (
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?

IC

Announcements