Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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.
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
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.
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:
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |