Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Client status over time

Hi,   I have a huge problem trying to get the client status over time.   Imagine a table like this: Date Client_ID Status 8/20/2019 1 S 8/19/2019 1 S 8/18/2019 1 S 8/20/20...
Need Help
  • Vvelarde's avatar
    Vvelarde
    7 years ago

    Anonymous 

     

    Hi, lets try with DAX (in Power Query will be a excellent solution too)

     

    1, Create a Calculated Column for Rank

     

    Ranking =
VAR _ClientID = 'Table'[Client_ID]
RETURN
    RANKX (
        FILTER ( 'Table'; 'Table'[Client_ID] = _ClientID ),
        'Table'[Date],
        ,
        ASC
    )

    2, Create a Calculated Column for RankPrev

     

    RankingPrev = 'Table'[Ranking]-1

    3. Create a Calculated Column for Previous Status

     

    PastStatus = 
VAR _ClientID='Table'[Client_ID]
VAR _RnkPrev='Table'[RankingPrev]
RETURN
CALCULATE(VALUES('Table'[Status]),FILTER(ALL('Table'),'Table'[Ranking]=_RnkPrev && 'Table'[Client_ID]=_ClientID))

    4. Create a Calculated Column for Status to Date

    (Review the conditions)

    Status_To_Date = 
SWITCH(TRUE(),'Table'[PastStatus]="S" && 'Table'[Status]="S","Active",
'Table'[PastStatus]="S" && 'Table'[Status]="N","Desertion",
'Table'[PastStatus]="N" && 'Table'[Status]="N","Inactive",
'Table'[PastStatus]="N" && 'Table'[Status]="S","Reactivated",
'Table'[PastStatus]=BLANK() && 'Table'[Status]="S","New Client",
'Table'[PastStatus]=BLANK() && 'Table'[Status]="N","Prospect",
'Table'[PastStatus]="N" && 'Table'[Status]=BLANK(),"Unknown ",
"Unknown")

    5. Test it with millions of rows

     

     

    Regards

     

    Victor

    Lima - Peru

Anonymous's avatar
Anonymous
Not applicable
7 years ago

OH YES, I will need help. Actually, someone offered a similar solution with the rank workaround over here:

https://community.powerbi.com/t5/Desktop/Customer-Status-over-time/m-p/760492#M366505

 

I even already have the rank variable in my table, but I think your solution about bringing the value past value to the actual row would be perfect. Please help me in this matter.

 

The Rank variable is the following:

```
Rank = RANKX
(FILTER('Data clientes lunes por 6 meses',
'Data clientes lunes por 6 meses'[Codigo_Cliente] = EARLIER('Data clientes lunes por 6 meses'[Codigo_Cliente])
&&
'Data clientes lunes por 6 meses'[Codigo_Ingreso] = EARLIER('Data clientes lunes por 6 meses'[Codigo_Ingreso]))
,'Data clientes lunes por 6 meses'[FechaCarga],,ASC,Dense) 
```
 
And the variable for Last Status is the following:
```
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))
```
 
Im almost over deadline and I really need to get this done.
 
 
 
Regards,
 
IC
Vvelarde's avatar
Vvelarde
Community Champion
7 years ago

Anonymous 

 

Hi, lets try with DAX (in Power Query will be a excellent solution too)

 

1, Create a Calculated Column for Rank

 

Ranking =
VAR _ClientID = 'Table'[Client_ID]
RETURN
    RANKX (
        FILTER ( 'Table'; 'Table'[Client_ID] = _ClientID ),
        'Table'[Date],
        ,
        ASC
    )

2, Create a Calculated Column for RankPrev

 

RankingPrev = 'Table'[Ranking]-1

3. Create a Calculated Column for Previous Status

 

PastStatus = 
VAR _ClientID='Table'[Client_ID]
VAR _RnkPrev='Table'[RankingPrev]
RETURN
CALCULATE(VALUES('Table'[Status]),FILTER(ALL('Table'),'Table'[Ranking]=_RnkPrev && 'Table'[Client_ID]=_ClientID))

4. Create a Calculated Column for Status to Date

(Review the conditions)

Status_To_Date = 
SWITCH(TRUE(),'Table'[PastStatus]="S" && 'Table'[Status]="S","Active",
'Table'[PastStatus]="S" && 'Table'[Status]="N","Desertion",
'Table'[PastStatus]="N" && 'Table'[Status]="N","Inactive",
'Table'[PastStatus]="N" && 'Table'[Status]="S","Reactivated",
'Table'[PastStatus]=BLANK() && 'Table'[Status]="S","New Client",
'Table'[PastStatus]=BLANK() && 'Table'[Status]="N","Prospect",
'Table'[PastStatus]="N" && 'Table'[Status]=BLANK(),"Unknown ",
"Unknown")

5. Test it with millions of rows

 

 

Regards

 

Victor

Lima - Peru

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Victor, usted esta casi a la altura de un dios. Mil gracias. Funciona super bien!!

     

    Sin embargo, ahora tengo otro problema. Estos valores son para un reporte donde quiero saber la deserción semanal. Cada uno de los ranking que obtuviste en la formula utilizada vendría siendo una semana. Es decir que el rango maximo seria la semana mas actualizada.

     

    Sabiendo esto, intenté filtrar por Ranking en Top 1, mas no pude obtener el valor deseado que es "Desercion semanal" en un visual de KPI.

     

    Podrias ayudarme a obtener el numero deseado? Si lograste hacer lo anterior, facilmente logras esto.

     

     

    Gracias anticipadas,

     

    IC

     

    Vvelarde 

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      7 years ago

      Anonymous 

       

      Hi, explain the case better please.

       

      Hola, explicame el caso mejor. 

       

      Regards

       

      Victor

      Lima - Peru

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Tomemos un caso como ejemplo:

        Cliente antiguo

        Aqui podemos ver el estado de este cliente en el tiempo. Lo que quisiera hacer es poder tomar el ultimo estado disponible (Mayor fecha existente) para cada cliente, contabilizar cada uno de los estados en esa fecha y crear una visualizacion estilo KPI para las deserciones.

         

        A fin de cuentas lo que quiero es esto:

        In a KPI Visual

         

        Pero de esta manera (solo para deserción):

        He escrito demasiado. Disculpame.

         

         

        Saludos y gracias anticipadas,

         

        Ivan Cespedes

        Rep. Dom.