Forum Discussion

Mubar22's avatar
Mubar22
Frequent Visitor
2 years ago

Customer with only One Purchase Across a Period

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 purchas...
data protection
Show and Tell
MFelix's avatar
MFelix
Icon for Super User rankSuper User
2 years ago

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's avatar
Mubar22
Frequent Visitor
2 years ago

Hi MFelix 

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

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    2 years ago

    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.