cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Guustc Regular Visitor
Regular Visitor

Cumulative sales new, lost and existing customers

I have a challenge concerning new, lost and existing customers and their sales. We want to explain our total sales difference by making different categories and assign customers to that categories.

Here is an short explanation about the definition of new sales and and example of data with customers, worth 10 euro turnover a month.

 

New sales.PNG

 

This is the exisiting formula, distracted from: http://www.daxpatterns.com/new-and-returning-custo​mers/

 

Omzet nieuwe klanten = 
CALCULATE (
    SUM ( Waardepost[Omzet netto] );
    FILTER (
        ADDCOLUMNS (
            VALUES(Waardepost[Bronnr]); 
            "PreviousSales"; 
                CALCULATE (
                    SUM ( Waardepost[Omzet netto] );
                    FILTER (
                        ALL(Kalender);
IF(ISFILTERED(Kalender[Jaar]);Kalender[Jaar] = max(Kalender[Jaar])-1;Kalender[Jaar]=year(now())-1) && IF(ISFILTERED(Kalender[Maand]);Kalender[Maand] <= max(Kalender[Maand]);Kalender[Maand] <= month(now())))
                ));
        [PreviousSales] <= 1);  
     FILTER( ALL(Kalender);IF(ISFILTERED(Kalender[Jaar]);Kalender[Jaar] = max(Kalender[Jaar]);Kalender[Jaar]=year(now())) && IF(ISFILTERED(Kalender[Maand]);Kalender[Maand] <= max(Kalender[Maand]);Kalender[Maand] <= month(now()))
))

 

 

The challange can be explained by taking the new customer in the data example and set the filters on month 6 in 2016. Regarding to the formula, there is now a total new sales of 60 (6 times 10), because in the same months 1-6 2015 there was no turnover. But, when I filter on the next month (7), the sales for this customer is not in the new sales category anymore, because in month 7 of 2015, there was a turnover of 10. This formula checks for a whole year, and now this sales is not counted anymore because there is a turnover in the first month of the calculation (july 2015). Actually, this is a good calculation, but my boss wants to see the cumulative new sales, so he can have a total count at the end of the year.

 

The challenge is in this is that I need to count the first 6 months for this new customer as new sales and from month 7 on, the customer will turn to an existing customer. I need different formulas for new sales, lost customers and existing customers for this calculation to be done.

4 REPLIES 4
Guustc Regular Visitor
Regular Visitor

Re: Cumulative sales new, lost and existing customers

I have tried some things to come up with a solution. First of all i thought i needed to create calculated columns to "freeze" the New, lost and existing sales based on the date in the row context.

 

Therefore I use this formula:

 

NewSales = 
CALCULATE(
    SUM ( Waardepost[Omzet netto] );
    FILTER (
        ADDCOLUMNS (
            VALUES(Waardepost[Bronnr]); 
            "PreviousSales"; 
                     Calculate(SUM ( Waardepost[Omzet netto] );       
                      FILTER(ALL(Kalender);Kalender[Datum] = SAMEPERIODLASTYEAR(Kalender[Datum]))));
        [PreviousSales] <= 1 ))

I actually get the error that I do not have enough memory to execute the calculation. I am now working on a 4GB memory laptop, but there is a possibility that I can install powerBI desktop on a server with 64GB of memory just to execute this calculation.

 

 

The question now is, is what i am doing the right option or are there any other possible solutions?

v-haibl-msft Super Contributor
Super Contributor

Re: Cumulative sales new, lost and existing customers

@Guustc

 

Could you please provide some sample data and the expected output to us? It should be helpful with sample data and above descriptions.

 

Best Regards,

Herbert

Guustc Regular Visitor
Regular Visitor

Re: Cumulative sales new, lost and existing customers

@v-haibl-msft thanks in advance for the help!

 

I have an excel file with sample data for six customers. I also have a tab where i analyze the data with a pivot table and explain the solution and expected output. Hope this helps: https://we.tl/NBibfHCnKm

Guustc Regular Visitor
Regular Visitor

Re: Cumulative sales new, lost and existing customers

Is there still nobody who can help me with this? It is really al problem that is usable for many organizations so I find it strange that nobody had this question before.. Smiley Happy