Month over Month Amount Calculation for each customer by stage

[pc2]

I have the following tables in Power BI Desktop:

• Date table
• Customer Billing table: Billing Date, Customer ID, Product, Billed Amount
• Customer table: Customer ID, First Billing Date, Cancellation Notice Date

For each customer, I'd like to group its billing into 3 categories: (1) New, (2) Churn, and (3) Existing

1. New: all billings from the customer in the 1st 120 days since the first date of billing would fall into this category
2. Churn: the customer would submit a cancellation notice.  The "Cancellation Notice Date" is captured and all billings since this date would be in the "Churn" category
3. Existing: all billings for the customer that doesn't fall into "New" and "Churn" would be categorized as "Existing"

My goal is to calculate the Month-over-month change in Billing $ for each customer and isolate this for each category: New, Existing, & Churn.  Then, I'd like to view this by month which aggregates each customer numbers for each month.

If customer does not have a Cancellation Notice Date, it would have just two categories: New and Existing.

I'm not sure how to handle this whether to add some calculated columns and/or do it with measures.  

I have the easy part, which calculates the Month over month change:

    Current Month Billing =        sum(Billing[AmountBilled])

    Previous Month Billing =        calculate ( [Current Month Billing], DATEADD('Date'[Date], -1, MONTH))

    MoM change = [Current Month Billing] - [Previous Month Billing]

I've tackled the next steps, but not getting the results I need (below table).  What's the best way to handle this?  I'd like to be able to drill down to Customer level and look at it by Product as well.

Month	MoM New	MoM Existing	MoM Churn
January
February
March
April
May
June
July
August

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[pc2]

@Ashish_Mathur 

Here's the link to the pbix file.

Thank you for your help.  I used @Anonymous 's Option #1 as the solution and it looks great, but I just have this issue to resolve.  I think the problem is with Month Over Month measure where take Current month billing minus Previous month billing.  However, this causes the Month (when Cancellation occurs) to have both "Existing" and "Churn" periods and one is missing Current billing and the other missing Previous month billing.  You'll see in the pbix file what I'm talking about.

I would like the Customer Status to be mutually exclusive, i.e. a given month should have one of the status, not both.

[Anonymous]

Hi @pc2 ,

I don't know what happened but my answer got posted on the Spanish forum but not here...

Yes, as you noticed a customer can have different status for a same month and as I understand you want only one value.

So here we have two things to take into account:

- the first for the churn status, as the cancellation date can occur during a month (we could have concurrently Existing and Churn)

- the second for the new status, as the end of new period can occur during a month (we could have concurrently New and Existing)

For this you need to change the code of the calculated column. The business logic will be:

For churn, as the cancellation date might occur during the month, you need to convert it to the start or end of month.
I went for end of month
e.g. 15-oct-2020 will be converted to 01-oct-2020 and all sales in october will be considered as churn

For new, it's not the First Billing Date that we will change but the First Billing Date + 120 days that we will change.

I used the following logic:

• if the day of First BIlling Date is less than or equal to 15, I round the date at the start of the current month
  e.g. 10-jan-2020 + 120 days => 10-may-2020 which becomes 1-may-2020 (c. 110 days for new period)
• if the day of First BIlling Date is greater than 15, I round the date at the start of the next month
  e.g. 20-jan-2020 + 120 days => 20-may-2020 which becomes 1-june-2020 (c. 130 days for new period)

Here is the code:

Customer Status = 

VAR OrderDate = Sales[Order Date]

// rounding end of "New status" period at start or end of month, to get all sales of one month under one status
VAR FirstBillingDate = RELATED( Customer[First Billing Date] )
VAR EndOfNewPeriodRaw = FirstBillingDate + 120
VAR EndOfNewPeriodStartOfMonth =
    IF( DAY(FirstBillingDate) <= 15,
        DATE( YEAR(EndOfNewPeriodRaw), MONTH(EndOfNewPeriodRaw), 1),
        EOMONTH(EndOfNewPeriodRaw,0)+1
    )

// same here, rounding cancellation date to start of month
VAR CancellationDateRaw = RELATED( Customer[Cancellation Notice Date] )
VAR CancellationDateStartOfMonth = DATE( YEAR( CancellationDateRaw ) , MONTH( CancellationDateRaw ) , 1 )

// calculating status
VAR CustomerCategory =
    SWITCH(
        TRUE(),
        OrderDate >= CancellationDateStartOfMonth, "Churn",
        OrderDate < EndOfNewPeriodStartOfMonth, "New",
        "Existing"
    )

RETURN CustomerCategory

Note: here I did make some choices regarding the business logic, it is up to you to change it if need be

Note 2: here if you absolutely need to have 120 days for the new period, then you will have to accept to have possibly the two different statuses "Existing" and "New" for one month for one customer

How can you check that everything works fine?

1. Create a measure that counts the number of distinct Customer statuses : DISTINCTCOUNT( ' Customer Billings'[Customer Status] )

2. Add it to a visual with the dimensionality of customer/year/month

3. Add a filter on the measure: greater than 1

=> Visual should be empty as you want no customer with more than one Customer Status for a month

Hope it helps and works. Does it?

Have a nice day

Thomas

[pc2]

@Anonymous 

Thank you so much.  I updated and have the following result below.

I put the pbix file here (file link).  Would you mind taking a look at it please?

I updated the calculated column, Customer Status, as you prescribed and I can see that each month has a unique status now.

However, the M2M calculation is still off.  You'll see that for Customer B, for the month of June 2019, following happens:

• Existing: Current Billing=$11,513, Previous Mo Billing=0, causing M2M=$11,513
• Churn: Current Billing=0, Previous Mo Billing=$11,377, causing M2M=$(11,377)

I think the issue is with M2M calculation for the overlapping month in question.

What I was hoping to see is below (I mocked it up in Excel):

[Anonymous]

Hi @p2c,

I had a close look at your screenshots and thanks for it, very clear.

After some thinking, I am not sure that the MoM computes the wrong figure. Actually it really depends on what we want it to compute.

If we look at the customer level, so at the 3 columns on the far right, we see that the total computed is still correct:

• +136$ in june 2019 as customer went from 11 377 to 11 513
• +6$ in june 2020 as customer went from 11 153 to 11 159

If we process the way you intended in your mockup, it will mean that your New category will add sales for customers becoming new, but not substracting the sales for customers that are leaving the category. Which will yield too high results.

Here's an example, let's say we look at what happened between February and March for the New category:

• Customer A: was and stays in New. He does +500$ in March (from 1000$ to 1500$)
• Customer B: was New in February, switch to Existing in March. Was 11 000$ in February. As per your explanation in the second screenshot, it would yield 0.
• Customer C: wasn't there in February, but is New in March. He does +10 000$

=> Result : the New category grew for 10 100$ in March. We have more and more new customers... when actually we do not as we did -500$ 

Maybe I am reading the whole situation from the wrong angle, please tell me what's your thinking on this one.

Best,

Thomas

[pc2]

@Anonymous 

Thanks for your reply.  I hear you that the Total M2M is correct and I agree.  But, I do think the logic on M2M needs to change to get what I'm after Thomas.  Please see picture below.

Is there any way to update DAX to calculate M2M so that the Prev Mo number is included in the right Status?

For June 2019, this is "Existing" status and I want both Current and Prev Mo Billing to show up here. 

Same thing for June 2020, this is "Churn" status and I want both Current and Prev Mo Billing to show up here.

[pc2]

@Anonymous 

Thomas, I think I may have solved this.  I'm doing some testing to ensure this is right though.. I'll let you know.

I've added another Calculated Column to grab Prev Mo Billing.  Then, instead of using time intelligence to calculate prev mo billing, I'm summing this new column to get the Prev Mo Biling, which is then used to calculated M2M.  It seems to give me what I want (see 2nd picture below).

Can you take a look and see if what I did makes sense and it should work at all times?

[pc2]

@Anonymous 

Thomas, my suggestion above has one flaw.  If the customer does not buy a specific product in later month, the Prev Mo Billing will not be captured.

For example, if customer buys in

• Feb 2020, Product 1 for $100, Product 2 for $50, 
• Mar 2020, Product 1 for 80 and no Product 2

The Prev Mo Billing calculated column for Mar 2020 will only pick up for Product 1 and Product 2 will be zero and this should be $50.  This is because there is no Product 2 line in Mar 2020.   😞

Any idea how to come around this?

[pc2]

@Anonymous 

I had a workaround and it works.  I created another billing table with all the dates in it, so I can calculate the M2M numbers correctly.

New Billing Table = CROSSJOIN( SUMMARIZE('Date', 'Date'[Date]), VALUES(Billing[Product]),   SUMMARIZE( Billing,  Billing[Customer_ID], Billing[Customer_Name]))

Then I added two calculated columns for Billing and Prev Mo Billing.

This works but it really ballooned the file size, but I don't see any other way of doing it for this problem I was solving.

Your solution and detail codes really helped me.  I'm marking yours as the solution provided.

Thanks again for your help. 

[Anonymous · Accepted solution]

Hi,

If I get it right, your customer has a status for each order he makes, based on the billing date, his first purchase date and an eventual cancellation date. This status can be new/existing/churn and can evolve over time for a customer.

In this case, I would go with a calculated column. But this scenario could be also solved using several dax measures or even other techniques. You can find the .pbix file containing what I have done.

Option 1: using a calculated column

Why would I go for a calculated column?

• better performance at query time, especially when addressing resource intensive calculations like this one that are not dynamic (for one row in Customer Billing, the value for the Customer Status would remain the same)
• less DAX code
• possibility to slice and dice on the Customer Status

Here as your status depends on the order date, I would add it to the Customer Billing table as it is the sole table containing this level of detail.

The code for 'Customer Billing'[Customer Status]

Customer Status = 

VAR OrderDate = Sales[Order Date]
VAR FirstBillingDate = RELATED( Customer[First Billing Date] )
VAR CancellationDate = RELATED( Customer[Cancellation Notice Date] )

VAR CustomerCategory =
    SWITCH(
        TRUE(),
        OrderDate >= CancellationDate, "Churn",
        OrderDate <= (FirstBillingDate + 120), "New",
        "Existing"
    )

RETURN CustomerCategory

I hope I reproduced your business logic in the SWITCH as you intended.

You then simply need to add your field to your visual and your MoM measure that you already authored and you get:

Results using Option 1

But are the results correct? I did a quick spot check looking at the customer detail:

Are the results correct? We can check for a customer

=> We see that In July, our customer spends 1222 and is considered as Existing (first billing in 22 of Feb.)

=> Then in August, the customer is still "Existing" but spends only 1100, which is indeed 122 less than the 1222 of July

Option 2, using DAX measures

If you can't or don't want to use a calculated column, you can still address the scenario with DAX measures. One of the main cons is that you'll have to write each measure three times: one for new, one for existing and one for churn.

The measures for Sales for New would be the following:

Sales for New = 

VAR SalesLinesMadeByNew = 
    FILTER(
        SUMMARIZE(Sales, Sales[Order Date] , Sales[CustomerKey], Customer[First Billing Date] ),
        Sales[Order Date] <= Customer[First Billing Date] + 120
    )

VAR SalesAmountMadeByNew =
    CALCULATE(
        [Sales Amount],
        SalesLinesMadeByNew
    )

Return SalesAmountMadeByNew    

Here I first compute a table that I will then use as a filter.

This table is stored in the variable SalesLinesMadeByNew and contains all the values of Customer/OrderDate that are considered New.

Note that there are surely other DAX alternatives to perform this calculation.

And you can see that Option 2 yields the same results as Option 1 :

I have added the calculated column as a column header and then the different measures. 

What are the performances differences?

On my machine the query to generate the report year/month with Sales + Sales MoM for each Customer Status:

• Option 1 => 60ms
• Option 2 => 240ms

Option 1 is 4x faster than Option 2

What do I do if my parameters have to change? For example I need to let the user decide what duration defines the "New Category" with a slicer (instead of using always 120 days).

Then option 1 doesn't work anymore and using DAX measures with a parameter would be imho the best option.

Best regards,

Thomas

[pc2]

@Anonymous 

Thanks so much your detailed response.

I tried Option #1, Calculated Column.  It's almost producing the right result, but there's one issue.  I've included the picture below.

I've isolated for a single customer to zero into the problem

This customer has a Cancellation Date of 6/22/2020.

As you can see, for the month of June 2020, M2M amount shows up under both "Churn" and "Existing".  The net amount is okay, since it nets out, but individually, it causes overstatement in one and understatement in the other one.  

How can I fix this issue?