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.
This seems like something simple and I'm struggling.
I feel like no matter how many books I'm reading (I have 4 PQ/PBI/DAX books in front of me), this is just not working.
I'm looking to list the names of new customers in a set of data. A list would be preferable, but I would even take a scalar value (like max or min of the text). I'd like it to be based on the filter context of my date selection. For example my date selection is looking at the last 12 months, and there are two new customers in the current month, I want it to display the names of the 2 new customers. Also, if they decide to change the date selection to include less than the current month, maybe two months ago, I'd like to display those new customers (lets say there are three) as if the current month was two months ago, if that makes sense.
This measure works for counting them over time so I can see month by month my count of new customers , - [New Customers] :=
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( 'Transactions Indexed'[Customer Index] ),
"PreviousSales", CALCULATE (
COUNTROWS ( 'Transactions Indexed' ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] < MIN ( 'Calendar'[Date] )
)
)
),
[PreviousSales] = 0
)
)
(This was based on Russo and Ferrari DAX pattern on churn)
But I wanted to show someone what makes up that number, what customers is it counting, how to provide a drillthrough to that table of data?
Again, I'll even settle for listing one of those new customer as a scalar text value, like can I say give me the max or min name of a customer within this date range the date tange being the latest month within the selection criteria by the user.
Thank you to the moon anyone who reads this message and actually understands me.
@Anonymous , refer if this can help
https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196
https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/
https://www.sqlbi.com/articles/computing-new-customers-in-dax/
Here is one way to do it
ListOfCustomers =
VAR mindate =
MIN ( 'Date'[Date] )
VAR currentcustomers =
VALUES ( Table[Customer] )
VAR previouscustomers =
CALCULATETABLE (
VALUES ( Table[Customer] ),
ALL ( 'Date' ),
'Date'[Date] <= mindate
)
VAR newcustomers =
EXCEPT ( currentcustomers, previouscustomers )
RETURN
CONCATENATEX ( newcustomers, Table[Customer], "; " )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering 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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |