Month over Month Amount Calculation for each customer by stage

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