This seems like it should be such a simple operation.
I have a requirement where I need to count the distinct Customer IDs of Customers that are new. Said another way, count the distinct customer ID where the order date matches the minimum order date of that customer (and several other dimensions).
I have found this to be nearly impossible.
I am able to get the correct count, and put it into a card. I'm even able to cross filter that card by other dimensions. But I need to take it a step further and show which months the New Customers appeared in.
I am aware that using a CALCULATEDTABLE, getting the minimum date by customer & my required dimensions, would solve my problem. Unfortunately, I am working in a Semantic Model, which I am unable to update.
That doesn't change that this seems like such a simple requirement, and I sincerely hope that something so seemingly simple can be done with measures.
Below is my code.
- I group the minimum Dates by the required dimensions, adding the other dimensions I need for cross filtering
[__NewAccounts_A_FirstOrderDate] =
var maxContextDate = CALCULATE( MAX( Dates[Date] ), ALLSELECTED( 'Sales' ) )
var filteredSales =
ADDCOLUMNS(
SUMMARIZE(
FILTER(
CALCULATETABLE(
Sales,
REMOVEFILTERS( Dates[Date] )
, REMOVEFILTERS( 'Product Details' )
, REMOVEFILTERS( 'Product Packaging' )
),
[C/E] > 0 &&
Sales[DeliveryDate] >= DATE( YEAR( maxContextDate ) - 1, 1, 1 )
),
Supplier[Supplier], // Group by SUPPLIER
Customer[Customer No.], // Group by Customer
Branch[Branch], // Group by Branch
"Min Customer Date", MIN( Dates[Date] ) // Calculate Minimum Delivery Date by Group
)
, "GB Brand", SELECTEDVALUE( 'Product Details'[GB Brand] )
, "Package Type", SELECTEDVALUE( 'Product Packaging'[Package Type] )
)
var Result =
CALCULATE(
MINX(
FILTER(
filteredSales,
[Min Customer Date] >= DATE( YEAR( maxContextDate ), 1, 1) && // Ensure the date is in the same year as max context date
[Min Customer Date] <= maxContextDate
),
[Min Customer Date] // Return the minimum date
)
, REMOVEFILTERS( Dates[Date] )
)
RETURN Result- I determine if the customer's first Order Date comes in the same year of the Context Date
[__NewAccounts_B_FirstOrderInContext]
var maxContextDate = CALCULATE( MAX( Dates[Date] ), ALLSELECTED( 'Sales' ) )
return
IF(
[__NewAccounts_A_FirstOrderDate] >= DATE( YEAR( maxContextDate ), 1, 1)
&& [__NewAccounts_A_FirstOrderDate] <= maxContextDate,
1, // Eligible New Account
0 // Ineligible New Account
)3. We count the distinct Customer IDs (via COUNTX( FILTER ( VALUES ( ... ), ... ), ... ); aka Customer IDs where the Min Date falls within the proper context, with a separate calculation for cross filtering
__NewAccounts_C_DistinctNewCustomerCount
VAR newAccounts =
CALCULATE (
COUNTX (
// Only count customers with a new account in the current year
FILTER ( VALUES ( Customer[Customer No.] ), [__NewAccounts_B_FirstOrderInContext] = 1 ),
// Count unique Customer Numbers
Customer[Customer No.]
),
// Remove external filters on Sales but retain the current user context
ALLSELECTED( Sales )
)
// Step 2: Calculate the number of new accounts that also have cross-filtering applied
VAR newAccounts_CF =
CALCULATE (
COUNTX (
FILTER (
VALUES ( Customer[Customer No.] ),
[__NewAccounts_B_FirstOrderInContext] = 1 // Only count customers with a new account in the current year
&& [___NewAccounts_CrossFilter] > 0 // Check Cross Filtering from Brand Sales
),
Customer[Customer No.]
)-- , ALLSELECTED( Sales )
)
// Step 3: Check if there is a cross-filter on 'Product Details' or 'Product Packaging'.
// If so, return the new accounts count with the cross-filter applied (newAccounts_CF),
// otherwise return the count without cross-filtering (newAccounts).
VAR result =
IF(
ISCROSSFILTERED( 'Product Details' ) || ISCROSSFILTERED( 'Product Packaging' ) ,
// Add 0 to force conversion to a numeric result
newAccounts_CF + 0,
newAccounts + 0
)
return resultYet, when I attempt to plot this data on a Line chart, it shows the Total count for each month. I don't get it.
This to me should work.
Can anyone smarter than me provide any assistance here?
