Condition for counting new customers

[DimaMD]

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

[DimaMD]

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]

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

Hi @tamerj1 ok i will try the new solution and let you know the result thanks

[tamerj1 · Accepted solution]

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 ) )

[DimaMD]

@tamerj1  Hi, on the first screenshot you can see that we chose calendar dates of 2022 and 2023, so the calculation was made correctly.

But as you can see on screenshot 2 we chose only year 2023 and mesure dont see that this was already a new customer because he bought something in 2022

Also for some reason, as you can see on the bottom table, when it doesnt have months on the upper level, table dont consider this customer as a new one.

I have attached a new file with extended data for 2 years

[tamerj1]

@DimaMD 

That depends on your business logic. If you want it to behave this way just replace ALLSELECTED with ALL

What does it mean to remove the Month from the filter context?!!

the logic of your requirement is all based on month granularity. Without having the month in the filter context the whole calculation has no meaning. 

[DimaMD]

@tamerj1 Thanks for understanding, I tried to replace Allselect with ALL and it worked to check if the client was in Novy in the month when our condition worked. Ok, the measure works, let's change the concept of the matrix and put the customer's prefact😁I think they will be fine in this format .
Thank you very much, as they say in Ukraine, Peaceful sky

[tamerj1]

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

[DimaMD]

Ні, @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]

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

@tamerj1 Hi, Friend, maybe you have some ideas?
How are you?

[tamerj1]

@DimaMD 

Hi friend. I read you post yesterday and yes I do have some ideas but still searching for time 😅

[DimaMD]

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