cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
icespedes Member
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:

 

image.pngCurrent Dataset

 

image.pngStatus Logic

 

image.pngGoal

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

5 REPLIES 5
Community Support Team
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")))

1.PNG

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

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

 

Could you add it?

 

Thanks in advance,

 

IC

Community Support Team
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" ))
    )
)

5.PNG

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
icespedes Member
Member

Re: Customer Status over time

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:

image.png

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 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)