Forum Discussion
Problem: DAX Patterns New Customers -- customer orders seem to disappear in detail
Problem: DAX Patterns New Customers -- customer orders seem to disappear in detail
Business case: My user wants to see a matrix visual that shows customers who made their first ever order to our company this year. It might look something like below:
This slicer has the field 'Sheet1'[Order Date] in it.
The summary matrix has the 'Sheet1'[Customer Key] for rows and [New Customer Orders] for Values.
The detail table has the following columns: 'Sheet1'[Customer Key], 'Sheet1'[Order Date], 'Sheet1'[Order Number], [New Customer Orders]. I intended for New Customer Orders to filter the table to new customers for 2023, but it is filtering to those new customers' orders that are associated with their first order date in 2023.
The summary table is correct, but the detail table seems to be omitting some of the new customers' orders that were submitted this year. The user would like to see every order submitted by the new customer this year, just not the orders associated with that new customers' first order date.
I used SQL BI's New and Returning Customers Pattern, but it doesn't quite seem to fit what I am doing: https://www.daxpatterns.com/new-and-returning-customers/
My user, for example, doesn't want break-outs of new customers by month. She just wants a count of the orders submitted so far this year by customers who made their first order in 2023.
This is the DAX I wrote based off of the Dynamic Absolute pattern:
I think my problem has sometihng to do with the [Date New Customer] variable filtering to the customers' earliest dates, but I'm not sure how to tell DAX to make sure to include the new customers' orders for all of 2023 in the Detail table.
Dummy model screenshot:
Note that Sheet1 has three columns, Customer Key, Order Date, and Order Number.
There is an inactive relationship between Sheet1's Order Date and Date's Date field.
Dummy data:
| Order Date | Customer Key | Order Number |
| 5/5/2023 | 1 | A |
| 5/5/2023 | 1 | B |
| 5/7/2023 | 1 | C |
| 3/1/2022 | 2 | D |
| 4/1/2023 | 2 | E |
| 4/2/2023 | 3 | F |
| 4/2/2023 | 3 | G |
| 4/3/2023 | 4 | H |
| 4/5/2023 | 4 | I |
| 4/7/2023 | 4 | J |
| 4/8/2023 | 4 | K |
2 Replies
Anonymous , Please refer to approaches I have use, change periods as per need
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q
amitchandak I'm sorry, but I'm not seeing how this helps with my problem.
For my dummy data, I adapted one of your formulas like so:
(Assume there is a slicer on screen where the user can select single 'Date'[Year] values)Year To Date =
CALCULATE(
[Total Orders]
,DATESINPERIOD('Date'[Date],max('Date'[Date]),-1,YEAR)
, USERELATIONSHIP('Sheet1'[Order Date], 'Date'[Date])
)Prior to Year To Date =
CALCULATE(
[Total Orders]
,DATESINPERIOD('Date'[Date],MIN('Date'[Date]),-30,YEAR)
, USERELATIONSHIP('Sheet1'[Order Date], 'Date'[Date])
)
I am able to successfully get to a count of customers by adapting the SUMX:New Customer This Period =SUMX(VALUES('Sheet1'[Customer Key]), IF(ISBLANK([Prior to Year To Date]) && NOT(ISBLANK([NEW Year To Date])), 1, BLANK()))
However, this also seems to return a different detail table than I want. The total of new customers (3) is correct, but the table detail is not correct.- As you can see below, Customer 2's order that started in 2023 is pulled in, but I don't want Customer 2 included at all because Customer 2 had an order in 2022 and is therefore not a new customer.
This is the result table I am looking for:Dummy data:
Order Date Customer Key Order Number 5/5/2023 1 A 5/5/2023 1 B 5/7/2023 1 C 4/2/2023 3 F 4/2/2023 3 G 4/3/2023 4 H 4/5/2023 4 I 4/7/2023 4 J 4/8/2023 4 K
