Plot New Customer Counts by Month

VBAmazing — Fri, 18 Oct 2024 20:17:59 GMT

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 result

Yet, 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?

Re: Plot New Customer Counts by Month

lbendlin — Fri, 18 Oct 2024 21:54:13 GMT

https://www.daxpatterns.com/new-and-returning-customers/

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Plot New Customer Counts by Month in DAX Commands and Tips

Plot New Customer Counts by Month

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

Re: Plot New Customer Counts by Month