Question

I want to calculate the distinct count of active customers but only for the last three completed months—how can I do this in DAX?
I need a DAX measure that calculates the distinct count of active customers, but it should only consider the last three fully completed months. What is the best approach using DATEADD, CALCULATE, or FILTER to ensure accurate customer count based on date filtering?

Answer 1

To calculate the distinct count of active customers for the last three fully completed months in Power BI using DAX, follow this approach:

DAX Measure for Last 3 Completed Months' Active Customers

Active Customers (Last 3 Months) = 
VAR MaxDate = MAX( 'Date'[Date] )
VAR LastCompletedMonth = EOMONTH( MaxDate, -1 ) -- Last full month end
VAR ThreeMonthsAgo = EOMONTH( LastCompletedMonth, -2 ) -- 3 months back

RETURN 
CALCULATE( 
    DISTINCTCOUNT( 'Sales'[CustomerID] ),
    'Date'[Date] > ThreeMonthsAgo && 'Date'[Date] <= LastCompletedMonth
)

Explanation:

Discovering the Last Completed Month :

EOMONTH(MAX(Date),-1)-It gives the last day of the previous completed month.

Finding the Beginning of the 3-Month Window:

EOMONTH(LastCompletedMonth, -2)-Calculates the last day of the month that was three months ago.

Filtering the Data to the Right Range:

It uses CALCULATE() to count distinct CustomerIDs, but only within the last three completed months.