count duplicate dates per Customer ID

[Laufer_Israel]

Hi all,

I have the below data:

ID     Date     Order Type

1      09/12      regular

1      09/13      regular

1      09/14      regular

1      09/15      regular

1      09/15       digital

The ID is a customer ID which I have 7,000 customers in my data.

I would like to count the duplicate dates within every customer (ID) AND only if one of the duplicate orders are digital.

i.e. if I have two records at the same day but both are regular or both are digital this is irelevant.

At the end, the result I'm looking for on the above example is: there is one day with two orders and one of them are digital.

Can someone help please?

Many thanks in advance!

[Laufer_Israel]

Can someone help?

[v-yingjl · Accepted solution]

Hi @Laufer_Israel ,

Based on your description, you can create this measrue to count:

Count =
VAR tab =
    FILTER (
        ALL ( 'Table' ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[ID] = EARLIER ( 'Table'[ID] )
                    && 'Table'[Date] = EARLIER ( 'Table'[Date] )
            )
        ) >= 2
    )
RETURN
    COUNTX (
        FILTER (
            tab,
            [ID]
                IN DISTINCT ( 'Table'[ID] )
                    && [Date]
                        IN DISTINCT ( 'Table'[Date] )
                            && [Order Type] = "regular"
        ),
        [Order Type]
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Laufer_Israel]

I will try to be more clear:

Regular order means that the customer ordered stock directly from the sales man in a specific day. a Digital order is an order that the customer conducted in advance (for a specific date).

When I see in the data two orders for the same day when one is regular and the other is digital it means that the digital order wasn't anoght for the customer and he asked the sales man for an immidiate additional order.

Thats what I'm looking for - I'm trying to understand how many regular orders apears at the same day of the digital order.

[amitchandak]

@Laufer_Israel , expected output is not clear

A measure like this

sumx(Summarize(Table, Table[ID], "_1", distinctcount(Table[Date])),[_1]-1)

[Laufer_Israel]

As I mentioned, I have 7,000 customers. every customer conducting an order which can be regular or digital order..

What I would like to know is to identify the number of events (count of days) that each customer conducted both regular and digital order at the same day.