Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Hi,
I'd do this
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.
@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.
After re-working Ashish's solution it is now operating as it should! Thank you! I'll mark this a solved.
You are welcome.
Sounds good @aschillinger20
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingConsider 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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingFirst 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |