Creating Calculated Table of Previous Customers in Power BI

The code snippet is utilizing DAX (Data Analysis Expressions) in Power BI to create a calculated table PreviousCustomers that stores all customers per month who have been in the system prior to the current month.

Let's break it down into each component.

CALCULATETABLE

CALCULATETABLE is a function that evaluates a table expression in a context modified by the given filters. It is a powerful function to change the context where your calculation is taking place. Here, it is used to create a new table PreviousCustomers based on another table AllCustomersPerMonth, but only including rows that satisfy the condition specified in the FILTER part.

FILTER

FILTER is a function that returns a table that includes only rows that satisfy the specified condition. Here, it is used to filter AllCustomersPerMonth, meaning it will only include rows in AllCustomersPerMonth that satisfy the condition [YearMonth] < EARLIER( AllCustomersPerMonth[YearMonth] ).

EARLIER

EARLIER is a function that returns the earlier value that the specified column had in the context. In this case, it's used to compare the value of the YearMonth column of AllCustomersPerMonth with its previous value.

[YearMonth] < EARLIER( AllCustomersPerMonth[YearMonth] )

This condition is specifying that the YearMonth of each row in AllCustomersPerMonth must be less than the previous YearMonth value (i.e., it should be a row from a month prior to the current month).

As a result of this entire DAX expression, the PreviousCustomers table will contain all customers that were registered prior to the current month.

This sort of calculated table could be useful for different analyses, for example, to calculate customer retention or churn rates.

For a deeper understanding of DAX expressions and advanced analytic techniques in Power BI, I recommend going through the relevant courses offered by Enterprise DNA Platform.