MIGRATION RATE

[ANM_97]

Hello everyone!

I try to calculate the migration rate of credits in the "DEP" state during a year.

For this I need to check the evolution of the clients who were in the "CUR" state at the beginning of the year and became "DEP".

New customers may appear during a year (example: 105 and 106). These should not be taken into account.

If a customer with the status "CUR" has become at least once "DEP" he will be considered "DEP" at the end of the year.

The client migration rate, in the "DEP" state, is calculated as follows:
Number of customers who entered the "DEP" state during a year / Total number of customers who were "CUR" at the beginning of the year.
-> 4/5 = 0.8
How can I do this calculation in PowerBi?

[v-alq-msft · Accepted solution]

Hello, @ANM_97

According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.

Mesa:

You can create a measure as follows.

Migration Rate = 
var tab =
SUMMARIZE(
    'Table',
    'Table'[Customer ID],
    "Flag1",
    IF(
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[State]=BLANK()
            )
        )>0,
        0,1
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Flag2",
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&
                [State]="CUR"
            )
        )=
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Customer ID]=EARLIER('Table'[Customer ID])
            )
        ),0,1
    )
)
return
DIVIDE(
    COUNTROWS(
         FILTER(
             newtab,
             [Flag1]=1&&
             [Flag2]=1
         )
    ),
    COUNTROWS(
        FILTER(
            newtab,
            [Flag1]=1
        )
    )
)

Result:

Best regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[ANM_97]

Thank you ! @v-alq-msft 
I don't think I did well. I should get about 3% on the original data, but I get 99% if I apply this measure.

[v-alq-msft]

Hi, @ANM_97 

You may replace the 'ALL' with 'ALLSELECTED' to see if it works.

Migration Rate = 
var tab =
SUMMARIZE(
    'Table',
    'Table'[Customer ID],
    "Flag1",
    IF(
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[State]=BLANK()
            )
        )>0,
        0,1
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Flag2",
    IF(
        COUNTROWS(
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&
                [State]="CUR"
            )
        )=
        COUNTROWS(
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Customer ID]=EARLIER('Table'[Customer ID])
            )
        ),0,1
    )
)
return
DIVIDE(
    COUNTROWS(
         FILTER(
             newtab,
             [Flag1]=1&&
             [Flag2]=1
         )
    ),
    COUNTROWS(
        FILTER(
            newtab,
            [Flag1]=1
        )
    )
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ANM_97]

@v-alq-msft

I'm not getting the right result.
Two measures could be built:
1. Save the number of clients with "CUR" status at the beginning of the period.
2. Save the number of clients who had "CUR" status at the beginning of the period, but became "DEP" at least once during a year.

The migration rate will be:
the number of clients who have had "DEP" status at least once during a year, divided by the number of clients with "CUR" status at the beginning of the period.

[ANM_97]

I know how to identify those who were in the "CUR" state at the beginning of the period, but how can I see which of them entered the "DEP" state and count them, without taking into account the new customers that appeared during the year..

Count_CUR =

var first = FIRSTDATE( DIM_DATE[DATE] )

return

CALCULATE(DISTINCTCOUNT(DIM_CUSTOMERS[CUSTOMER_ID]),DIM_CUSTOMERS[DEFAULT_STATUS]in {"CUR"}, DIM_DATE[DATE] = first)