Calculation of New Customers Per Month

To calculate the number of new customers each month, you can utilize a combination of DAX functions, including CALCULATETABLE, EXCEPT, SUMMARIZE, EARLIER, and FILTER.

First, we create a summarized table with all distinct clients per month, then create another table which filters all previous month buyers for each month. Finally using EXCEPT function we find all distinct clients this month that didn't buy anything in the previous months, which are the new clients.

Here is a step-by-step breakdown:

Step 1:

Create a summarized table that lists all distinct customers per month:

    AllCustomersPerMonth = 
        SUMMARIZE(
            'Sales', 
            'Sales'[YearMonth], 
            "Customers", VALUES('Sales'[CustomerId])
        )

Step 2:

Create a table that filters all previous month customers:

    PreviousCustomers = 
        CALCULATETABLE(
            AllCustomersPerMonth,
            FILTER(
            AllCustomersPerMonth, 
            [YearMonth] < EARLIER( AllCustomersPerMonth[YearMonth] )
            )
        )

Step 3:

Calculate the number of new customers by subtracting the previous customers from the total customers:

    NewCustomers = 
        COUNTX(
            EXCEPT( AllCustomersPerMonth[Customers],  PreviousCustomers[Customers]), 
            [Customers]
        )

The EXCEPT function returns the customers that are in the current month but not in the previous months, which are the new customers. The COUNTX function then counts these new customers.

Unit Testing

For accuracy, it's always crucial to test this function on your existing data. An effective approach to testing this would be to manually calculate the number of new customers for a given month and compare it to the result from this function. In event of inconsistencies, troubleshooting would involve checking that the data was correctly summarized in the first step and correctly filtered in the second step.

Learning Resources

For further understanding of how to work with DAX and Power BI, consider the many courses offered on Enterprise DNA Platform, especially those focusing on DAX functions and data modelling.