count all related records in a month

[Anonymous]

Hi

I have contacts with related orders (N:1)

Orders have a field "created on" (datefield)

I need to count and display all contacts that have more than one order created per month

How can I fulfill this?

Thnaks in advance for the help!

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Here is my original data table:

Create a ‘Count_order’ column in ‘Orders’ table, the column is used to count the orders for every contact per month：

Count_order = 
CALCULATE(
    COUNT(Orders[Create on]),
    FILTER(
        Orders,
        Orders[Create on].[Month] = EARLIER(Orders[Create on].[Month]) 
        &&
        Orders[Create on].[Year] = EARLIER(Orders[Create on].[Year]) 
        &&
        Orders[Contacts_ID] = EARLIER(Orders[Contacts_ID])
    )
)

Then, create a ‘if over 1’ column in ‘Orders’ table, the column is used to judge if the order number is over 1:

if over 1 = 
IF(
    Orders[Count_order] >1,
    TRUE(),
    FALSE()
)

At last, create a ‘if over 1’ column in ‘Contacts’ table:

if over 1 = 
LOOKUPVALUE(
    Orders[if over 1],
    Orders[Contacts_ID], Contacts[Contacts_ID],
    Orders[if over 1], TRUE()
    
)

If you want to show the number of the contacts  who have more than 1 orders  per month, you can do like this:

Count_contact = 
CALCULATE(
    DISTINCTCOUNT(Orders[Contacts_ID]),
    FILTER(
        Orders,
        Orders[Count_order] >1
    )
) 

Best Regards,
Lionel Chen

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