cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Count New Customers during time period

I'm struggling to figure out what seems like a simple task. I would like to display a card that shows the number of "new customers" in a given time period. I'm able to generate a list of accounts in a table by using the following:

 

isFirstOrder =
IF( CALCULATE(MIN('Sales Data'[invoice_date]),ALLEXCEPT('Sales Data','Sales Data'[Customer]))=MAX('Sales Data'[invoice_date]), 1, BLANK())
 
I just want to show the number of new customers in a card. Any help would be greatly appreciated.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Count New Customers during time period

Hi,

I'd do this

  1. Create a Calendar Table and write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number
  2. Build a relationship from the Invoice_date column of your Sales Data Table to the Date column of your Calendar Table
  3. In your Table/filter/slicer, drag Year and Month from the Calendar Table
  4. Write these measures:

Date of first interaction = Calculate(min('Sales Data'[invoice_date]),Datesbetween(Calendar[Date],minx(all(Calendar),Calendar[Date]),max(Calendar[Date])))

New customers = Countrows(filter(values('Sales Data'[Customer]),[Date of first interaction]>=min(calendar[Date])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Highlighted
Super User VI
Super User VI

Re: Count New Customers during time period

Consider this pseudo-code because I just threw some logic together. I'd need data to really nail this down and make sure I had the ALL* logic where it belongs, but this is the logic flow I'd work through.

  1. varMinnvoiceDate gets the earliest invoice date for a customer
  2. CustomerCount just filters the table where the max invoice date = the varMinInvoice date and counts the rows.

Again, I'd need to see data.  I'm half thinking now that COUNTROWS needs to be replaced with a COUNTX iterator.

New Customer Count =
VAR varMinInvoiceDate =
    MINX(
        ALLEXCEPT(
            'Sales Data',
            'Sales Data'[Customer]
        ),
        'Sales Data'[invoice_date]
    )
VAR CustomerCount =
    COUNTROWS(
        FILTER(
            ALLEXCEPT(
                'Sales Data',
                'Sales Data'[Customer]
            ),
            'Sales Data'[invoice_date] = varMinInvoiceDate
        )
    )
RETURN
    CustomerCount

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper II
Helper II

Re: Count New Customers during time period

First and foremost thank you for the quick reply!! Its humbling to know how quickly others can grasp this stuff. 

 

With Countrows I get a total of 1 for the month of April when it should be 4. I tried Countx, but i'm getting the following error: 

 

"Too few arguments were passed to the COUNTX function, the minimum argument for the function is 2." 

Highlighted
Super User VI
Super User VI

Re: Count New Customers during time period

Just add the invoice date. CountX has the following format
= COUNTX(Table, Expression). Just put the invoice date or similar in the expression. You are just counting how many times the expression shows up.

 

To further help, we'd really need some data.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User V
Super User V

Re: Count New Customers during time period

Hi,

I'd do this

  1. Create a Calendar Table and write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number
  2. Build a relationship from the Invoice_date column of your Sales Data Table to the Date column of your Calendar Table
  3. In your Table/filter/slicer, drag Year and Month from the Calendar Table
  4. Write these measures:

Date of first interaction = Calculate(min('Sales Data'[invoice_date]),Datesbetween(Calendar[Date],minx(all(Calendar),Calendar[Date]),max(Calendar[Date])))

New customers = Countrows(filter(values('Sales Data'[Customer]),[Date of first interaction]>=min(calendar[Date])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper II
Helper II

Re: Count New Customers during time period

@Ashish_Mathur For some reason this reporting a number that is much higher than it should be. I built a table visualization to look at "date of first interaction" data and it is definitly showing me the first date it finds per customer. So i believe that measure is working as expected. Manualy counting new customers from April 2020 there are 5. when i drop "New Customers" onto a card it comes up with 136. 

 

I'll work on building a test book that i can share tommorow. 

Highlighted
Super User VI
Super User VI

Re: Count New Customers during time period

Sounds good @aschillinger20 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper II
Helper II

Re: Count New Customers during time period

@edhans : After working around with this a little using "count rows" if i set up a matrix with year as the column, month as the row, and "new customer count" as the value. I see that April 2020 correctly displays 5. If i drop "new customer count" on a card and add slicer for year and month it shows 145 for some reason. 

 

I'll build a quick table that i can share tommorow. I truely appreciate the help thus far!! 

 

Highlighted
Super User VI
Super User VI

Re: Count New Customers during time period

This is why I use DAX Studio for this kind of thing. It is difficult to see why something is returning unexpected results in just Power BI Desktop, but DAX Studio is another bit of a learning curve. What it would do though is return the table you see for what you are putting in the card before you count the data.

 

Looking forward to your sample data.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User V
Super User V

Re: Count New Customers during time period

Hi,

Ensure that the Year and Month selection is set to interact with the card visual.  If it still does not help, then share the link from where i can download your PBI file with my formulas already written in that file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors