Aggregate distinct customers

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.