Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have the following tables in Power BI Desktop:
For each customer, I'd like to group its billing into 3 categories: (1) New, (2) Churn, and (3) 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 |
Solved! Go to 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.
Why would I go for a calculated column?
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:
But are the results correct? I did a quick spot check looking at the customer detail:
=> 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
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 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
Hi,
Share the link from where i can download your PBI file.
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.
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:
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
@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:
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):
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:
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:
=> 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
@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.
@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?
@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
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?
@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.
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.
Why would I go for a calculated column?
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:
But are the results correct? I did a quick spot check looking at the customer detail:
=> 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
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 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
@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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |