A data modeler is creating a Power BI report for a subscription service. The model includes a 'Subscriptions' table with 'SubscriptionID', 'StartDate', and 'EndDate'. The modeler needs to calculate the number of active subscriptions at the end of each month. Which DAX function or pattern is best suited for this scenario?
- ACOUNTROWS(FILTER('Subscriptions', 'Subscriptions'[StartDate] <= MAX('Date'[Date])))
- BCOUNTX(FILTER('Subscriptions', 'Subscriptions'[EndDate] >= TODAY()), 'Subscriptions'[SubscriptionID])
- CDATESBETWEEN('Date'[Date], MIN('Subscriptions'[StartDate]), MAX('Subscriptions'[EndDate]))
- DCALCULATE(COUNTROWS('Subscriptions'), FILTER(ALL('Subscriptions'), 'Subscriptions'[StartDate] <= MAX('Date'[Date]) && 'Subscriptions'[EndDate] >= MAX('Date'[Date])))
Show answer & explanationAnswer & explanation
Correct answer: D. CALCULATE(COUNTROWS('Subscriptions'), FILTER(ALL('Subscriptions'), 'Subscriptions'[StartDate] <= MAX('Date'[Date]) && 'Subscriptions'[EndDate] >= MAX('Date'[Date])))
This pattern accurately captures active subscriptions for a period-end snapshot. It uses CALCULATE to modify the filter context, FILTER to define the active criteria for each subscription row, and ALL to remove existing filters on the 'Subscriptions' table before applying the new filter, ensuring all subscriptions are considered.
Why the other options are wrong
- A. This only checks the start date, not the end date, so it would count all subscriptions that ever started by that date.
- B. This uses TODAY() which is a fixed date, not dynamic for each month-end, and only checks the end date.
- C. DATESBETWEEN returns a table of dates, it does not count subscriptions based on start/end dates.
Snapshot Measures (Period-End)
Snapshot measures calculate a value as it stood at a specific point in time, typically at the end of a period (e.g., month-end, year-end), by evaluating conditions against that specific date.
- Requires defining a specific 'snapshot date' within the context.
- Involves filtering data based on conditions relative to the snapshot date.
- Often uses CALCULATE with FILTER to modify context and evaluate conditions for each row.
- Crucial for tracking inventory, headcounts, or active subscriptions over time.
Memory trick: Snapshots capture the moment, like a camera at the end of a month.