Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to calulate Lost Customers every month

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:

 

YearMonthOrganisationVolumes
2017-10Customer11
2017-11Customer213
2017-12Customer318
2017-12Customer42
2018-02Customer513
2018-03Customer63
2018-04Customer74
2018-05Customer55
2018-06Customer57
2018-07Customer55
2018-08Customer717
2018-09Customer512
2018-10Customer613
2018-11Customer68
2018-12Customer515
2018-13Customer77

 

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

 

 2018-092018-102018-11
No. of lost Customers112

 

 

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

 

OrganisationLostMonthLastTransaction
Customer12018-092017-10
Customer22018-102017-11
Customer32018-112017-12
Customer42018-112017-12

 

Thanks in advance!

Need Help
Show and Tell

8 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous

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

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous

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

         

        Best Regards

        Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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