Code Simplifier | VBA

Simplified Code for Counting New Customers

The "NewCustomers" measure in DAX is used to count the number of unique customers in the current context that are not found in previous months. It utilizes the EXCEPT function to compare the customer table in the current context with the customer tab...


Empty image or helper icon

This Query related with thread ""

Prompt

// Creating a measure for New Customers
NewCustomers :=
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerID] ), // Counting unique customers
    FILTER ( // Filtering the Sales table
        ALL ( Sales ), // Considering all rows irrespective of an existing filter 
        Sales[CustomerID] // Sales records for a Certain Customer   
        IN CALCULATETABLE ( // Creating a derived table 
            VALUES ( Sales[CustomerID] ), // Unique Customers
            FILTER ( 
                ALL ( Sales[Date] ), // Taking all dates ignoring the existing filter
                Sales[Date] <= MAX ( Sales[Date]) // Taking dates less than or equal to the maximum date which is the considered period (month and year context)
            ),
            Sales[SalesAmount] // Sales must be greater than zero
        )
    )
)


isnt there a much easier way to write this, using EXCEPT function, comparing a virtual table of customer is current context versus all previous customers in prior monthsnwhich you could easily get using a CALCULATETABLE FUNCTION

Answer

Simplified Code

NewCustomers :=
COUNTROWS (
    EXCEPT (
        VALUES ( Sales[CustomerID] ), // Unique Customers in current context
        CALCULATETABLE ( // All previous customers in prior months
            VALUES ( Sales[CustomerID] ),
            DATESBETWEEN (
                Sales[Date], // Date column
                MIN ( Sales[Date] ), // Start of the period
                MAX ( Sales[Date] ) - 1 // End of the period
            )
        )
    )
)

Explanation

  • The simplified code uses the EXCEPT function to compare a virtual table of customers in the current context (using VALUES) with all previous customers in prior months (using CALCULATETABLE).
  • DATESBETWEEN is used to specify the date range for the prior months, starting from the minimum date to the maximum date minus 1 day.
  • The result is the count of unique customers from the current context that are not found in the prior months.

Unit Testing

You can perform unit testing on the simplified code using the following scenarios:

Scenario 1: No prior customers

  • Sales table has 3 unique CustomerIDs: A, B, C
  • The current context has CustomerIDs A and B
  • Expected result for NewCustomers measure: 0
NewCustomers_Test1 :=
COUNTROWS (
    EXCEPT (
        VALUES ( {"A", "B"} ),
        CALCULATETABLE (
            VALUES ( Sales[CustomerID] ),
            DATESBETWEEN (
                Sales[Date],
                MIN ( Sales[Date] ),
                MAX ( Sales[Date] ) - 1
            )
        )
    )
)

Scenario 2: Some prior customers

  • Sales table has 4 unique CustomerIDs: A, B, C, D
  • The current context has CustomerIDs A, B, and C
  • Prior months have CustomerIDs A, B, C, and D
  • Expected result for NewCustomers measure: 0 (all customers in the current context are also in the prior months)
NewCustomers_Test2 :=
COUNTROWS (
    EXCEPT (
        VALUES ( {"A", "B", "C"} ),
        CALCULATETABLE (
            VALUES ( Sales[CustomerID] ),
            DATESBETWEEN (
                Sales[Date],
                MIN ( Sales[Date] ),
                MAX ( Sales[Date] ) - 1
            )
        )
    )
)

Scenario 3: New customers in the current month

  • Sales table has 5 unique CustomerIDs: A, B, C, D, E
  • The current context has CustomerIDs A, B, C, and E
  • Prior months have CustomerIDs A, B, and C
  • Expected result for NewCustomers measure: 1 (customer E is a new customer in the current month)
NewCustomers_Test3 :=
COUNTROWS (
    EXCEPT (
        VALUES ( {"A", "B", "C", "E"} ),
        CALCULATETABLE (
            VALUES ( Sales[CustomerID] ),
            DATESBETWEEN (
                Sales[Date],
                MIN ( Sales[Date] ),
                MAX ( Sales[Date] ) - 1
            )
        )
    )
)

Note: You may need to adjust the test scenarios and DAX expressions based on your specific data model and requirements.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

This DAX code provides a simplified way to calculate the count of new customers in Power BI or Power Pivot. It compares the unique customers in the current context with the unique customers in previous months, by using the EXCEPT function. The DATESBETWEEN function is used to define the date range of the previous months. The result is the count of unique customers from the current context that are not present in the previous months. The code also includes unit test scenarios to validate the measure.

More Code Simplifiers
Apache Flink Code SimplifierApache Pig Code SimplifierAzure Data Factory Code SimplifierC/C++ Code SimplifierCouchDB Code SimplifierDAX Code SimplifierExcel Code SimplifierFirebase Code SimplifierGoogle BigQuery Code SimplifierGoogle Sheets Code SimplifierGraphQL Code SimplifierHive Code SimplifierJava Code SimplifierJavaScript Code SimplifierJulia Code SimplifierLua Code SimplifierM (Power Query) Code SimplifierMATLAB Code SimplifierMongoDB Code SimplifierOracle Code SimplifierPostgreSQL Code SimplifierPower BI Code SimplifierPython Code SimplifierR Code SimplifierRedis Code SimplifierRegex Code SimplifierRuby Code SimplifierSAS Code SimplifierScala Code SimplifierShell Code SimplifierSPSS Code SimplifierSQL Code SimplifierSQLite Code SimplifierStata Code SimplifierTableau Code SimplifierVBA Code Simplifier