Customer with only One Purchase Across a Period

[Mubar22]

Hello Power BI Dev,,

I have sales table and I want to count the unique customers that only purchase once across a period. i.e let's say the period is between 2023-2024 so the customer only purchase once irrespective of the month.

I have this measure written, but it's not giving the right output.

OneTimeCustomers = 
VAR MaxDate = MAX('Date Table'[Date])
RETURN
    COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE('Sales', 'Sales'[companyID]),
                    "Total Orders", DISTINCTCOUNT('Sales'[invoiceID])
                ),
                [Total Orders] = 1 && MIN('Sales'[OrderDate]) < MaxDate
            ))

Attached is a screenshot of what I want to achieve using the filter pane on the visual.

Kindly help out.
Thanks

[v-shex-msft]

Hi @Mubar22 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[MFelix]

Hi @Mubar22 ,

You are not removing the filter context of the dates to get the full time period so when you get the max and minimum date you get for that specific month and not for the full period of sales try the following code:

OneTimeCustomers = 
VAR MaxDate = MAX('Date Table'[Date])
RETURN
    COUNTROWS(
            FILTER(
               CALCULATETABLE( ADDCOLUMNS(
                    SUMMARIZE('Sales', 'Sales'[companyID]),
                    "Total Orders", DISTINCTCOUNT('Sales'[invoiceID])
                ), ALLSELECTED(Date Table)),
                [Total Orders] = 1 && MIN('Sales'[OrderDate]) < MaxDate
            ))

[Mubar22]

Hi @MFelix 

This doesn't work, it's returning blank.

[MFelix]

Hi @Mubar22

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.