Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aschillinger20
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.
11 REPLIES 11
Ashish_Mathur
Super User
Super User

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/

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

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/

After re-working Ashish's solution it is now operating as it should! Thank you! I'll mark this a solved. 

You are welcome.


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

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
edhans
Super User
Super User

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

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

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

@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!! 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.