Cumulative sales new, lost and existing customers

[Guustc]

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.

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.

[Guustc]

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]

@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]

@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]

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.. 🙂