A data modeler is creating a Power BI report that analyzes customer activity over time. The 'Customers' table contains 'CustomerID' and 'SignupDate'. The 'Activity' table contains 'ActivityID', 'CustomerID', and 'ActivityDate'. There is a one-to-many relationship from 'Customers[CustomerID]' to 'Activity[CustomerID]'. The modeler needs to calculate the number of active customers at the end of each month. An 'Active Customer' is defined as any customer who has had at least one activity within the last 90 days from the end of the current month. Which DAX expression should be used?
- ACALCULATE(DISTINCTCOUNT(Activity[CustomerID]), DATESBETWEEN(Activity[ActivityDate], MAX('Date'[Date]) - 90, MAX('Date'[Date])))
- BCALCULATE(DISTINCTCOUNT(Activity[CustomerID]), DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -90, DAY))
- CVAR EndOfMonth = LASTDATE('Date'[Date]) RETURN CALCULATE(DISTINCTCOUNT(Activity[CustomerID]), FILTER(ALL(Activity), Activity[ActivityDate] >= EndOfMonth - 90 && Activity[ActivityDate] <= EndOfMonth))
- DVAR EndOfMonth = LASTDATE('Date'[Date]) VAR DateRange = DATESBETWEEN('Date'[Date], EndOfMonth - 90, EndOfMonth) RETURN CALCULATE(DISTINCTCOUNT(Activity[CustomerID]), DateRange)
Show answer & explanationAnswer & explanation
Correct answer: D. VAR EndOfMonth = LASTDATE('Date'[Date]) VAR DateRange = DATESBETWEEN('Date'[Date], EndOfMonth - 90, EndOfMonth) RETURN CALCULATE(DISTINCTCOUNT(Activity[CustomerID]), DateRange)
This measure requires defining a rolling 90-day window relative to the end of each month (which is determined by the filter context from the 'Date' table). Option C correctly captures this. 'LASTDATE('Date'[Date])' determines the end of the current month in the filter context. 'DATESBETWEEN('Date'[Date], EndOfMonth - 90, EndOfMonth)' then generates a table of all dates within that 90-day window. When this date table is passed as a filter to CALCULATE, it filters the 'Activity' table through the 'Date' table's relationship, ensuring that only activities within the specified 90-day period are considered for the distinct count of 'CustomerID'.
Why the other options are wrong
- A. Using 'DATESBETWEEN(Activity[ActivityDate], ...)' directly on the fact table's date column can be inefficient. It's generally better to apply date filters to the 'Date' dimension table, which then propagates to the fact table. Also, 'MAX('Date'[Date])' might not always align with the 'end of month' in a monthly context, whereas LASTDATE('Date'[Date]') is more precise.
- B. DATESINPERIOD works on the 'Date' table, but applying it directly to filter 'Activity[CustomerID]' without explicitly linking the filter context to 'ActivityDate' might not yield correct results, especially if the 'ActivityDate' column is not directly filtered by the 'Date' table for this specific measure.
- C. This expression uses FILTER(ALL(Activity), ...) which removes all existing filters from 'Activity', then applies a row-level filter. While it might work, it's generally less efficient than leveraging time intelligence functions (like DATESBETWEEN) that work on the 'Date' dimension table and propagate filters more efficiently.
DAX Time Intelligence (Rolling Window)
DAX time intelligence functions are used to perform date-related calculations such as year-to-date, previous year, or rolling averages. For rolling windows, functions like DATESBETWEEN or DATESINPERIOD are combined with CALCULATE to define flexible date ranges for aggregation.
- Requires a marked Date table.
- CALCULATE is essential for changing date-based filter context.
- DATESBETWEEN defines a custom date range.
- LASTDATE or ENDOFMONTH helps define the anchor point for the window.
- Efficiently filters data for complex date comparisons.
Memory trick: Rolling windows: Anchor, range, then CALCULATE the count.