How to calulate Lost Customers every month

[Anonymous]

Hello!

I'm in stuck with my problem, searched trough the forum posts but did not find suitable solution.

I need to calculate how much lost customers do I have every month. Lost customer in my case is customer whose last volumes were 12 months ago.

Here is how my data model looks like:

YearMonth	Organisation	Volumes
2017-10	Customer1	1
2017-11	Customer2	13
2017-12	Customer3	18
2017-12	Customer4	2
2018-02	Customer5	13
2018-03	Customer6	3
2018-04	Customer7	4
2018-05	Customer5	5
2018-06	Customer5	7
2018-07	Customer5	5
2018-08	Customer7	17
2018-09	Customer5	12
2018-10	Customer6	13
2018-11	Customer6	8
2018-12	Customer5	15
2018-13	Customer7	7

so, according above data expected result is to have such report:

	2018-09	2018-10	2018-11
No. of lost Customers	1	1	2

and get a list of customers names with period when it was lost and period when last transaction happened, smtg like this:

Organisation	LostMonth	LastTransaction
Customer1	2018-09	2017-10
Customer2	2018-10	2017-11
Customer3	2018-11	2017-12
Customer4	2018-11	2017-12

Thanks in advance!

[v-juanli-msft]

Hi @Anonymous

As for the "year-month" table in my last post, you could create it in excel manually, then import to power bi.

Or you could create it in power bu desktop with this formula

Table =
VAR calendartable =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 12, 31 ) ),
        "year", YEAR ( [Date] ),
        "month", MONTH ( [Date] ),
        "year-month", YEAR ( [Date] ) & "-"
            & FORMAT ( MONTH ( [Date] ), "mm" ),
        "year/month/day", MONTH ( [Date] ) & "/"
            & 1
            & "/"
            & YEAR ( [Date] )
    )
RETURN
    SUMMARIZE ( calendartable, [year], [month], [year-month], [year/month/day] )

You could see the result of this formula in my pbix called "Table"

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous  

calcuated measures and columns in "Customers"

calculated column :
date/year/month = RELATED('year-month'[date])

measure:
last_date =
CALCULATE (
    MAX ( Sales[date/year/month] ),
    FILTER ( ALL ( Sales ), [Customer ID] = MAX ( [Customer ID] ) )
)


last-month = FORMAT([last_date],"yyyy-mm")

lost = [last_date]+364

calculated column:
lost-month = FORMAT([lost],"yyyy-mm")

measure:
no-lost cus = CALCULATE(DISTINCTCOUNT(Sales[Customer ID]),ALLEXCEPT(Sales,Sales[lost-month]))

revenue = SUM(Sales[Volumes])

lost customer revenue =
CALCULATE (
    SUM ( Sales[Volumes] ),
    FILTER (
        ALL ( Sales ),
        [Customer ID] = MAX ( [Customer ID] )
            && [date/year/month] <= [lost]
            && [date/year/month] >= [last_date]
    )
)

Please refer to my pbix,

If you have any problem, please let me know.

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft, thanks for your input. So I have found LostCustomers by period. Now I need to calculate lost customer revenue (I have a measure TotalRevenue already calculated) last 12 months before they were lost. 

For example. Customer lost on 2018-Oct, so I need to know how much total revenue they had in period from 2017-Nov up to 2018-Oct.

thanks in advance!

[v-juanli-msft]

Hi @Anonymous

I update my post, please have a looto see if it helps you in this scenario.

Best Regards

Maggie

[Anonymous]

Hello @v-juanli-msft,

Here is my data model: https://1drv.ms/f/s!AkvcTGBsDXLLgw9ogz2E0IEA1x_y

I tried various ways to find out lost customers and their volumes and revenue 12 months prior lost but did not found proper solution. Kindly please assist.

Tomas

[LivioLanzo]

Hello @Anonymous

check out my example here: https://1drv.ms/u/s!AiiWkkwHZChHj1Pow1W04Iiu_L8K

[AlB]

Hi @Anonymous

Have a look at this. It's complex but I think it will be useful for what you are trying to do.

[Anonymous]

thank you @AlB, @LivioLanzo it was useful.