Code Simplifier | VBA

NewCustomers Measure

Calculate the count of new customers using a simplified code that compares unique customers in the current context with all previous customers in prior months.


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 // Creating a measure for New Customers NewCustomers := CALCULATE ( DISTINCTCOUNT ( Sales[CustomerID] ), EXCEPT ( VALUES ( Sales[CustomerID] ), // Unique Customers in the current context CALCULATETABLE ( VALUES ( Sales[CustomerID] ), // Unique Customers in prior months ALL ( Sales[Date] ), // Ignoring the existing filter Sales[Date] <= MAX ( Sales[Date] ) ) ) )

The simplified code uses the EXCEPT function to compare a virtual table of customers in the current context with all previous customers in prior months. The EXCEPT function returns the unique customers that are in the first virtual table but not in the second virtual table.

Unit Testing To test the functionality of the simplified code, you can use the following unit tests:

Test 1: When there are no sales records

  • Set the Sales table to an empty table
  • Verify that the NewCustomers measure returns 0

Test 2: When there are sales records for existing customers only

  • Set the Sales table to a table with sales records for existing customers only
  • Verify that the NewCustomers measure returns 0

Test 3: When there are sales records for new customers only

  • Set the Sales table to a table with sales records for new customers only
  • Verify that the NewCustomers measure returns the count of new customers

Test 4: When there are sales records for both existing and new customers

  • Set the Sales table to a table with sales records for both existing and new customers
  • Verify that the NewCustomers measure returns the count of new customers

By performing these unit tests, you can ensure that the simplified code accurately calculates the count of new customers.

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 simplified code uses the EXCEPT function to compare a virtual table of customers in the current context with all previous customers in prior months. It returns the unique customers that are in the first virtual table but not in the second virtual table. To test the functionality of the code, you can perform unit tests with different scenarios such as no sales records, sales records for existing customers only, sales records for new customers only, and sales records for both existing and new customers. These unit tests will help ensure that the code accurately calculates the count of new customers.

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