Cumulative Sales New and lost 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-customers/

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.

I think the solution is maybe in making calculated columns for each month with new or existing, but maybe the solution can be more simple. Anyone who can help me with this?

[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-customers/

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.

I think the solution is maybe in making calculated columns for each month with new or existing, but maybe the solution can be more simple. Anyone who can help me with this?