Aggregate distinct customers

These are each connected bidirectionally with the customer number.

Why bidirectional?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hi azaterol 

To achieve this in Power BI, you need to create a measure that counts distinct customers who made a purchase on any given day within the current month, but ensures that customers who have already made a purchase earlier in the month are not counted again. Here’s how you can do it:

Step-by-Step Instructions

1. Create a Calendar Table:

   • Ensure you have a calendar table in your model.

Calendar = CALENDAR(MIN('InvoiceTable'[Date]), MAX('InvoiceTable'[Date]))
​

• Create a Relationship:

  • Create a relationship between your Calendar table and the InvoiceTable using the date column.

• Create a Measure to Count Distinct Customers

DistinctCustomers = 
CALCULATE(
    DISTINCTCOUNT('InvoiceTable'[CustomerNumber]),
    FILTER(
        'InvoiceTable',
        'InvoiceTable'[Date] <= MAX('Calendar'[Date]) &&
        'InvoiceTable'[Date] >= STARTOFMONTH('Calendar'[Date])
    )
)

• Create a Measure to Exclude Previously Counted Customers

UniquePurchasingCustomers = 
CALCULATE(
    DISTINCTCOUNT('InvoiceTable'[CustomerNumber]),
    FILTER(
        'InvoiceTable',
        'InvoiceTable'[Date] = MAX('Calendar'[Date]) &&
        NOT 'InvoiceTable'[CustomerNumber] IN 
        CALCULATETABLE(
            VALUES('InvoiceTable'[CustomerNumber]),
            FILTER(
                'InvoiceTable',
                'InvoiceTable'[Date] < MAX('Calendar'[Date]) &&
                'InvoiceTable'[Date] >= STARTOFMONTH('Calendar'[Date])
            )
        )
    )
)
​

• Add the Measure to Your Matrix:

  • Use the UniquePurchasingCustomers measure in your matrix visual.

Example Visualization

• Rows: Year-Month-Day
• Values: UniquePurchasingCustomers

This setup ensures that each customer is only counted once per month, regardless of how many times they make a purchase within that month.