How it works...

The data in the R02_Table fact table represents sample data for something like an e-commerce website where purchases are intermittent as opposed to something like a monthly or yearly subscription service.

For returning customers, the first two lines simply store the values for the month and year within the current context. This context is provided by the rows and columns of the matrix visualization.

The next step is to store the distinct values for all customers in previous months and years in the __PreviousCustomers variable. We do this by changing the context to all rows in our R03_Table fact table using the ALL function. We then use the FILTER function to get all of the rows in the table prior to the current month and year. We then use SELECTCOLUMNS to select the Customer column. Finally, we use the DISTINCT function to return only the unique values. We use an identical process to return current customers in the __CurrentCustomers variable, except that no filtering is necessary since the rows for current customers are already in context.

Once we know the values for all previous customers and current customers, we can compute returning customers and store these in the __ReturningCustomers variable. To do this, we use INTERSECT.

Note that to identify returning customers, you may be tempted to use the EXCEPT DAX function. The EXCEPT DAX function returns the rows in the table specified in the first parameter that are not in the table specified in the second parameter. However, if we used the __CurrentCustomers variable as the first parameter and __PreviousCustomers as the second parameter, we would get both new customers and not returning customers. Conversely, if we used __PreviousCustomers as the first parameter and __CurrentCustomers as the second parameter, we would get a table of non-returning customers.

Once we know the returning customers for a month, we can simply calculate new customers by subtracting the number of returning customers in a month from the total number of customers in a month.