Condition for counting new customers

Hello community
My task is to count new customers according to the following condition
The total number of purchases must be more than 3 purchases and the number of purchased goods must be more than 5,000 units
For example, we have a customer with ID 71, he made 1 purchase of 7,500 units in January and 2 purchases of 14,000 units in February, such a customer will be new. If the customer had more than 3 purchases and more than 5,000 units in the current month, we consider him as a new customer, but do not indicate in the next month that he is a new customer.

I tried to write the following calculation, but for some reason it does not give me the desired result.

New Customers = 
VAR CurrentMonth =
    MAX('Dataes'[Month])
VAR _PreviousMonth =
    EOMONTH(CurrentMonth, -1)
VAR NewCustomers =
    CALCULATETABLE(
        VALUES('TableFact'[ID_Customers]),
        'Dataes'[Month] = CurrentMonth,
        'TableFact'[Amount] >= 5000 &&
        COUNTROWS('TableFact') >= 3
    )
VAR ReturningCustomers =
    CALCULATETABLE(
       VALUES('TableFact'[ID_Customers]),
        'Dataes'[Month] = CurrentMonth,
        'TableFact'[Amount] < 5000,
        'Dataes'[Month] = _PreviousMonth,
        'TableFact'[Amount] >= 5000 &&
        COUNTROWS('TableFact') >= 3
    )
VAR NewCustomersWithMoreThanThreePurchases =
    CALCULATETABLE(
        NewCustomers,
        'TableFact'[ID_Customers] IN
            CALCULATETABLE(
                VALUES('TableFact'[ID_Customers]),
                'Dataes'[Month] = CurrentMonth,
                'TableFact'[Amount] >= 5000 &&
                COUNTROWS('TableFact') >= 3
            )
    )    
RETURN
    COUNTROWS(NewCustomersWithMoreThanThreePurchases) - COUNTROWS(ReturningCustomers)

What measure should I use for the desired result

NewCustomer.pbix75 KB

@dax @help

tamerj1

3 years ago

Hi DimaMD
I just tried. Seems to be working

New Customers - TJ 2 = 
VAR SelectedDates =
    ALLSELECTED ( Dataes )
VAR CurrentMonth =
    MAX ( 'Dataes'[YearMonth] )
VAR CurrentCustomers =
    VALUES ( TableFact[ID_Customers] )
VAR CurrentNewCustomers =
    FILTER (
        CurrentCustomers,
        CALCULATE (
            SUM ( TableFact[Amount] ),
            Dataes[YearMonth] <= CurrentMonth,
            SelectedDates
        ) >= 5000
            && CALCULATE (
                COUNTROWS ( TableFact ),
                Dataes[YearMonth] <= CurrentMonth,
                SelectedDates
            ) >= 3
    )
VAR PreviousCustomers =
    CALCULATETABLE (
        VALUES ( 'TableFact'[ID_Customers] ),
        'Dataes'[YearMonth] < CurrentMonth,
        SelectedDates
    )
VAR PreviousNewCustomers =
    FILTER (
        PreviousCustomers,
        CALCULATE (
            SUM ( TableFact[Amount] ),
            Dataes[YearMonth] < CurrentMonth,
            SelectedDates
        ) >= 5000
            && CALCULATE (
                COUNTROWS ( TableFact ),
                Dataes[YearMonth] < CurrentMonth,
                SelectedDates
            ) >= 3
    )
RETURN
    COUNTROWS ( EXCEPT ( CurrentNewCustomers, PreviousNewCustomers ) )

13 Replies

DimaMD
Solution Sage
3 years ago
tamerj1 Hi, Friend, maybe you have some ideas?
How are you?
- tamerj1
  Community Champion
  3 years ago
  DimaMD
  
  Hi friend. I read you post yesterday and yes I do have some ideas but still searching for time 😅
  - DimaMD
    Solution Sage
    3 years ago
    tamerj1 Thanks, I'll wait, I try to chat with ChatGPT but it gives me rough estimates. He is not human and cannot understand logic 😁
tamerj1
Community Champion
3 years ago
DimaMD
Please refer to updated sample file with the proposed solution

New Customers - TJ = VAR SelectedDates = ALLSELECTED ( Dataes ) VAR CurrentMonth = MAX ( 'Dataes'[YearMonth] ) VAR CurrentCustomers = VALUES ( TableFact[ID_Customers] ) VAR CurrentNewCustomers = FILTER ( CurrentCustomers, CALCULATE ( SUM ( TableFact[Amount] ), Dataes[YearMonth] <= CurrentMonth, SelectedDates ) >= 5000 && CALCULATE ( COUNTROWS ( TableFact ), Dataes[YearMonth] <= CurrentMonth, SelectedDates ) >= 3 ) VAR OneMonthBefore = MAXX ( FILTER ( ALLSELECTED ( Dataes[YearMonth] ), Dataes[YearMonth] < CurrentMonth ), Dataes[YearMonth] ) VAR CustomersOneMonthBefore = CALCULATETABLE ( VALUES ( 'TableFact'[ID_Customers] ), 'Dataes'[YearMonth] = OneMonthBefore, SelectedDates ) VAR NewCustomersOneMonthBefore = FILTER ( CustomersOneMonthBefore, CALCULATE ( SUM ( TableFact[Amount] ), Dataes[YearMonth] < CurrentMonth, SelectedDates ) >= 5000 && CALCULATE ( COUNTROWS ( TableFact ), Dataes[YearMonth] < CurrentMonth, SelectedDates ) >= 3 ) RETURN COUNTROWS ( EXCEPT ( CurrentNewCustomers, NewCustomersOneMonthBefore ) )
NewCustomer.pbix76 KB
- DimaMD
  Solution Sage
  3 years ago
  Ні, tamerj1 in cases where condition appears only once, it works.
  
  But in case (like on screenshot customer 531) when you can see that condition worked 2 times - on 2nd and 4th month, it worked twise. Our goal is if this customer was "new" once, he doesnt need to be "new" second time, So if he was new on "month 2" then on "month 4" he isn't new anymore.
  - tamerj1
    Community Champion
    3 years ago
    DimaMD
    
    Ok, that requirement was not clear as you mentioned "but do not indicate in the next month that he is a new customer". You should've said next months.
    However, I'm now driving back home so cannot test but you may try
    
    New Customers - TJ =
    VAR SelectedDates =
    ALLSELECTED ( Dataes )
    VAR CurrentMonth =
    MAX ( 'Dataes'[YearMonth] )
    VAR CurrentCustomers =
    VALUES ( TableFact[ID_Customers] )
    VAR CurrentNewCustomers =
    FILTER (
    CurrentCustomers,
    CALCULATE (
    SUM ( TableFact[Amount] ),
    Dataes[YearMonth] <= CurrentMonth,
    SelectedDates
    ) >= 5000
    && CALCULATE (
    COUNTROWS ( TableFact ),
    Dataes[YearMonth] <= CurrentMonth,
    SelectedDates
    ) >= 3
    )
    VAR PreviousCustomers =
    CALCULATETABLE (
    VALUES ( 'TableFact'[ID_Customers] ),
    'Dataes'[YearMonth] < CurrentMonth,
    SelectedDates
    )
    VAR PreviousNewCustomers =
    FILTER (
    PreviousCustomers,
    CALCULATE (
    SUM ( TableFact[Amount] ),
    Dataes[YearMonth] < CurrentMonth,
    SelectedDates
    ) >= 5000
    && CALCULATE (
    COUNTROWS ( TableFact ),
    Dataes[YearMonth] < CurrentMonth,
    SelectedDates
    ) >= 3
    )
    RETURN
    COUNTROWS ( EXCEPT ( CurrentNewCustomers, PreviousNewCustomers ) )
DimaMD
Solution Sage
3 years ago
hi tamerj1

VAR OneMonthBefore = MAXX ( FILTER ( ALLSELECTED ( Dataes[YearMonth] ), Dataes[YearMonth] <= CurrentMonth ), Dataes[YearMonth] )

Found an error in this change should have specified <=, after the change, the measure worked correctly
- tamerj1
  Community Champion
  3 years ago
  DimaMD
  
  With this change the code will retrieve the month if the current filer conext same as CurrentMonth!! I don't believe this will have any advantage. I have completely removed this variable in the 2nd proposed dax. Have you tried?
  - DimaMD
    Solution Sage
    3 years ago
    Hi tamerj1 ok i will try the new solution and let you know the result thanks

Forum Discussion

Condition for counting new customers

13 Replies

Related content

Count rows with condition

Conditional Formatting on Count %RT

Conditional count

Conditional Row count

Count rows with condition