Client status over time

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