Count customers between dates if they haven't purchased before

[Syndicate_Admin]

Good morning, everyone,

Thanks in advance for the help. I have a question for a long time and I do not know how to solve it. I work with a table that I simulate below.

I want to know how to count customers for dates that I mark on the calendar, as long as they have not bought before. Ahem: If I mark in the calendar the dates from 21/11 to 24/11, the number of different clients is 5. Now, if I march alone on 11/23, then it's 2. What I want to achieve is that if I mark the day 24/11 instead of counting 5, only count me 2 (customer 3 and 5), since customers 1, 2 and 4 have bought on previous dates.

I hope I haven't messed up too much.

Thank you again for your help.

Greetings

[v-jianboli-msft · Accepted solution]

Hi @Syndicate_Admin ,

Please try:

Measure2 =
VAR _a =
    MIN ( 'calendar'[Date] )
VAR _b =
    SUMMARIZE (
        'Table',
        'Table'[Clientes],
        "Flag",
            IF (
                CALCULATE (
                    COUNT ( 'Table'[Clientes] ),
                    FILTER (
                        ALL ( 'Table' ),
                        [Clientes] = EARLIER ( 'Table'[Clientes] )
                            && [Fecha compra] < _a
                    )
                ) > 0,
                0,
                1
            )
    )
RETURN
    SUMX ( _b, [Flag] )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Fantastic! Thank you very much for the solution!!!

[v-jianboli-msft]

Hi @Syndicate_Admin ,

According to your decription, if I select 2022-11-22 to 2022-11-24, the count of customers seems should be 2. 

Did I misunderstand?

If I didn't misunderstand, please try:

Measure =
VAR _a =
    SUMMARIZE (
        'Table',
        [Clientes],
        "Flag",
            IF (
                CALCULATE (
                    COUNT ( 'Table'[Clientes] ),
                    FILTER (
                        ALL ( 'Table' ),
                        [Clientes] = EARLIER ( 'Table'[Clientes] )
                            && [Fecha compra] <= MAX ( 'calendar'[Date] )
                    )
                ) > 1,
                0,
                1
            )
    )
VAR _b =
    SUMMARIZE (
        'Table',
        [Clientes],
        "Flag",
            IF (
                CALCULATE (
                    COUNT ( 'Table'[Clientes] ),
                    FILTER (
                        'Table',
                        [Clientes] = EARLIER ( 'Table'[Clientes] )
                            && [Fecha compra] <= MAX ( 'calendar'[Date] )
                    )
                ) > 1,
                0,
                1
            )
    )
VAR _c =
    COUNTX ( SELECTCOLUMNS ( 'calendar', "Sliered", [Date] ), [Sliered] )
RETURN
    IF ( _c = 1, SUMX ( _a, [Flag] ), SUMX ( _b, [Flag] ) )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Good morning

I apologize, I think I did not explain the problem well.

If I select from 2022-11-22 to 2022-11-24, the correct customer count should be 4, as the only customer who has purchased before that date is customer 1. (Purchase of 35 units on 11/21). See image marked in red.

Unlike your result, if you have to take for good the customer 2 and 4, since all purchases have been made within the period we have marked from 22/11 to 24/11. It does not matter that you have bought in several days as long as it is within the period we mark. If for example we marked from 23/11 to 24/11, then customer 2 would be left out because he made a purchase on 22/11.

I hope I have explained it now.

Thank you, this calculation is very important to me

[v-jianboli-msft]

Hi @Syndicate_Admin ,

Please try:

Measure = 
var _a = SUMMARIZE('Table',[Clientes],"Flag",IF(CALCULATE(COUNT('Table'[Clientes]),FILTER(ALL('Table'),[Clientes]=EARLIER('Table'[Clientes])&&[Fecha compra]<=MAX('calendar'[Date])))>1,0,1))
return SUMX(_a,[Flag])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Good afternoon

First of all, thank you for your reply. The measure created solves the problem half-heartedly. I think I did not explain my casuistry well.

If I select a single day, the measurement works perfectly, but if I filter by putting several days together, then the calculation does not do me well. I need that if I take several days, I count customers other than those days who have not bought the previous days.

As you can see in your example, if I select 3 days, the total count of customers who have bought in those 3 days, and had not done so before, according to the measure is 2.

This is wrong, it should be 4 because customer 1 had already purchased on the 21st.

 

I hope this explanation helps to see if you can solve it.

Thank you!

[Mikelytics]

@Syndicate_Admin 

Your welcome!

Can you please mark it as as solution? 🙂

[Mikelytics]

@Syndicate_Admin 

Can you please elaborate why you expect result 2 when selecting 11/23.

In your table on 11/23 client 4 and 1 bought something. But client already bought on 21/11. SO from my understanding the results would be 1 and not two? 

As you describe it the formula should do the following.

1) Go throught the customers who have bought in the selected time period

2) look whether the selected customers have bought something before 

3) if yes, then ignore them, if no then take them into account

if this is the requirement then the formula should look something like this:

Number of customers not bought before =
VAR var_MinDate =
    FIRSTDATE ( DateTable[Date] )
RETURN
    SUMX (
        VALUES ( FactTable[Clientes] ),
        VAR var_NumberOfSalesBeforeMinDate =
            CALCULATE (
                COUNTROWS ( FactTable ),
                ALL ( FactTable ),
                FactTable[Fecha compra] < var_MinDate
            )
        RETURN
            IF(var_NumberOfSalesBeforeMinDate > 0, 0, 1)
    )

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Syndicate_Admin]

Good afternoon

I have tried the formula and it gives me the same result without filtering the customers who would have already bought.

New CTES Test =

WHERE var_MinDate =

DATE(22,10,31)

RETURN

SUMX(

VALUES(Requests[Customer Code]),

WHERE var_NumberOfSalesBeforeMinDate =

CALCULATE (

COUNTROWS(Requests),

ALL(Requests),

Requests[Order Date] < var_MinDate

)

RETURN

IF(var_NumberOfSalesBeforeMinDate > 0, 0, 1)

)

[Syndicate_Admin]

Good morning Miguel,

You are absolutely right!! I was wrong, it was 1 the expected result for 23/11

A million thanks for the solution!!!