Aggregate distinct customers

[azaterol]

Hello everyone,

I have a matrix. I have two tables. A customer table and an invoice table.

These are each connected bidirectionally with the customer number.

I have the formula: purchasing POS = COUNTROWS('Customer table')

in a matrix the number of daily purchasing customers.

I have year-month-day as a line. The column is the measure purchasing POS.

Now I want the measure to aggregate. In addition to aggregating, the measure should not count up again what customers purchased on a previous day in the current month. In other words, if a customer has already ordered on a previous day in the current month, this customer should not be counted again.

SAMPLE Data: https://easyupload.io/wr38ek

I hope you can help me. 

[suparnababu8]

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.

[azaterol]

@suparnababu8 

Can you please stop using ChatGPT?

I went to the trouble of uploading an example so that I wouldn't receive an incorrect bot-generated answer.

Can someone give me a correct solution like the good old days without AI? I am very grateful for that. Thanks to everyone who helps.

[suparnababu8]

Hello @azaterol 

I'm not using chatGPT.

I am trying to solve your problem. Based on your input I given soultion for that. I think you misundertanded me. 

What you are expecting as a output I'm not clear, But as per my understanding, now I tried one solution for you and attaching here pbix file. sample_demo.pbix  

Don't judge other people without knowing what they do for you. 
Thanks!

[azaterol]

@suparnababu8Please stop commenting on my post. I say this with all politeness. Since I use ChatGPT from time to time myself, I know the type of solutions ChatGPT suggests. Here is another post from you that corresponds exactly to the ChatGPT pattern.

Again, your ChatGPT solution is absolutely useless. Please stop. Everyone notices it.

I hope that someone seriously thinks about my problem. I would like to thank everyone for their effort.

[lbendlin]

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-...

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/1447...

[azaterol]

hello @lbendlin ,

here is a sample: https://easyupload.io/wr38ek

What is wrong with bidirectional?

[lbendlin]

Here's a different view of your data model. That Documentation table looks weird. What's the story behind it?

What is the first buy column intended to achieve? There is also a data quality issue for Documentation Nr. 4996 - conflicting customer IDs.

Bidirectional = bad.  This here is Power BI, not Qlik.  Power BI wants Star schemas.