Need help with calculation of churn

[MrDigital]

Hey,

I think I need your help, because I am tackling a problem for too long now.

I need to calculate a "churn" based on sales orders. That means I basically have a list of Sales orders with their revenue for a specific date (month).

This looks something like this:

Sales order	Date	Revenue	Product
123145	01.01.2020	5	A
121346	01.01.2020	10	B
121346	01.02.2020	11	B
126657	01.02.2020	15	C

I now want to calculate the churn or let's say the revenue of the sales order that isn't there any longer for a specific date.

So, when I filter my data or look at my data for February, I want to see that a revenue of 5 ist lost in February.

Until now I didn't manage to do this, because when I filter or look at my data of February the sales order 123145 isn't there, there is now row for that date. So I didn't find a way to calculate the churn. I managed to calculate the revenue of the previous month with the PREVIOUS function and I also was able to classify the sales order as "new" or "repeat" for the current month using the RANKX function, but I can't calculate the churn.

I created a simple Excel file and a simple Power BI file with a pivoted version of the Excel data, maybe that helps in finding a solution.

Excel file: https://www.dropbox.com/s/vinc5wxvdct1ztx/SalesOrderChurn.xlsx?dl=0 

Power BI file: https://www.dropbox.com/s/4jbe5zd17ltyxs4/SalesOrderChurn.pbix?dl=0 

Thank you all!

Best regards and stay healthy,

Jan

[dax]

Hi @MrDigital , 

You could refer to my sample for details. If this is not what you want, please correct me and inform me more detailed information(such as your expected output and your sample data (OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MrDigital]

Hi, 

thank you very very much for your help!

I just checked your solution be expanding the sample data, because it doesn't exactly match my need.

But somehow I really seem to have trouble explaining what I want. Sorry for that.

I think you summed up the revenues for the missing sales orders, I just need to now the one missing revenue from last month. 

So let me try to explain it again with a picture. 

Looking at each sales order I want to know the single revenue that was there last month, but isn't there this month. 

If there would be two sales orders which revenue we lost in a month, then I do would need a sum.

But for this example it would be the following:

I need a value of 5 in February, a value of 15 in April and a value of 6 in June. 

I updated the Excel with this sample data:

https://www.dropbox.com/s/vinc5wxvdct1ztx/SalesOrderChurn.xlsx?dl=0

Thank you very very much!

[Anonymous]

First off, you should explain the calculation you want to make in more depth so that we understand it with all its idiosyncrasies. You know what you want because you've been working on this problem for a while now. We have no clue about what it is. So, please explain the calculations you want clearly to us.

It'll help us find the best solution and it'll help you think about it deeper once again. I've already witnessed people here who understood the way to solve a puzzle after they had explained it to others.

Thanks.

Best
D

[MrDigital]

Hi,

thank you. You are right. 

So, let's treat the sales order as one customer.

I want to calculate the revenue that we lost due to customers that no longer exist / cancelled their subscription.

The data that I have is a given list of sales orders (or let's say customer) and their revenue for a specific date:

Sales order	Date	Revenue	Product
123145	01.01.2020	5	A
121346	01.01.2020	10	B
121346	01.02.2020	11	B
126657	01.02.2020	15	C

If I want to analyze the data from February I want to calculate the revenue of the customer that doesn't exist any longer. In our example this ist the sales order 123145. The sales order 121346 generated revenue in Jan and Feb, so it means he didn't cancel his subscription, he is still paying. Sales order 126657 is a new customer in February. 

I need to calculate the revenue of the customer who no longer is a customer, aka 123145, which means a revenue of 5.

So I need to somehow filter the revenue column based on the fact that there is a sales order for the last but not for the current month. Since there is no sales order in the list for 123145 in February I can't generate a calculated column because after filtering for February there is no sales order for 123145 in the table. I would need to shift the current date one month back and then calculate if there is a sales order in the next month (aka the current month).

Is this more understandable? I need to sum up the revenue of all customers that left and for which I don't have an entry in the table for the current month or the selected month. 

Thanks for your help!

Jan

[Anonymous]

OK, Jan, that's a lot more understandable (and doable). It's not that hard to implement. Bear with me, please.

Best
D