Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is building a Power BI report for a subscription-based service. The model has a 'Subscriptions' fact table with 'SubscriptionID', 'StartDate', and 'EndDate'. The modeler needs to calculate the number of active subscriptions at the end of each month. Subscriptions are considered active if their 'StartDate' is on or before the month-end date and their 'EndDate' is after the month-end date. Which DAX pattern effectively calculates this 'snapshot' measure?
- AUtilize a many-to-many relationship between 'Subscriptions' and 'Date' to count intersections.
- BUse COUNTROWS with FILTER to check conditions against StartDate and EndDate relative to the month-end date.
- CUse DATESINPERIOD to define the month and then COUNTROWS.
- DCreate a calculated column in 'Subscriptions' to mark active status and then sum it.
Show answer & explanationAnswer & explanation
Correct answer: B. Use COUNTROWS with FILTER to check conditions against StartDate and EndDate relative to the month-end date.
To calculate a snapshot measure like 'active subscriptions at month-end', you need to count rows that meet specific criteria relative to a dynamic month-end date. This is best achieved using COUNTROWS combined with a FILTER function that checks both the StartDate and EndDate against the current month-end date in the filter context.
Why the other options are wrong
- A. A many-to-many relationship is generally complex and not required for this type of calculation; it would not inherently provide the logic for checking start/end dates against a month-end snapshot.
- C. DATESINPERIOD is used to generate a table of dates within a period; it doesn't directly provide the logic for checking subscription start/end dates against a specific month-end.
- D. A calculated column is static and cannot dynamically respond to different month-end dates in a report, making it unsuitable for a snapshot measure that changes per month.
Snapshot Measures (Period-End)
Measures that calculate a value (e.g., inventory, active subscriptions) at a specific point in time, typically the end of a period (day, month, quarter, year).
- Requires filtering fact data based on a period-end date.
- Often uses functions like EOMONTH, LASTDATE, or MAX to determine the period end.
- Involves checking conditions across start and end dates relative to the snapshot date.
Memory trick: Capture the moment: Filter by start/end relative to period end.