DAX Event-in-Progress Pattern: Calculating Overlapping Intervals, Headcount Snapshots, and Concurrent Activity Metrics Across Date Ranges

Introduction

Imagine you're an HR analyst who needs to answer this question: How many employees were actively employed on each day of last quarter? You have a table with hire dates and termination dates, but no daily snapshot of headcount. Or picture yourself in operations analytics: you need to know the peak number of simultaneous support tickets open at any given hour. Your data has open timestamps and close timestamps — nothing more.

These problems have something in common. They're not about point-in-time events. They're about intervals — periods of time during which something is true. An employee is "active" from their hire date to their termination date. A support ticket is "open" from creation to resolution. A hotel room is "occupied" from check-in to check-out. Standard aggregation functions like SUM or COUNT won't help you here, because the data doesn't have a row for every day the condition is true. What you need is the Event-in-Progress pattern — a DAX approach that lets you ask, at any point in time, "what was happening right now?"

By the end of this lesson, you'll be able to build sophisticated headcount, occupancy, and concurrent-activity measures that work correctly across any date range a slicer or filter might throw at them.

What you'll learn:

• How the Event-in-Progress pattern works conceptually and why standard approaches fail
• How to build a date-table-driven headcount snapshot measure from scratch
• How to extend the pattern to handle concurrent activity with time-of-day granularity
• How to handle edge cases: open-ended intervals, part-time periods, and multiple overlapping categories
• Performance considerations and when to choose alternative data modeling strategies

Prerequisites

You should be comfortable with DAX filter context and how CALCULATE modifies it — if you need a refresher, the lesson on Understanding DAX: CALCULATE and Filter Context covers the mechanics thoroughly. You should also understand how iterator functions like SUMX work at a conceptual level; DAX Iterators Explained: How SUMX, AVERAGEX, and MAXX Evaluate Row by Row to Solve Problems SUM Cannot is a good primer if you haven't read it. Familiarity with DAX Relationships 101: How Power BI Filters Flow Across Tables and Why It Matters for Your Measures will also help, since the pattern deliberately works around relationships in a specific way.

The Problem with "Normal" Aggregation

Let's ground this in a real scenario. You work for a mid-sized company and have the following Employees table:

EmployeeID	Name	Department	HireDate	TerminationDate
1001	Sarah Chen	Engineering	2022-01-15	NULL
1002	Marcus Webb	Sales	2021-06-01	2023-08-31
1003	Priya Nair	Engineering	2023-03-20	NULL
1004	Jordan Lee	HR	2020-09-01	2022-12-31
1005	Alex Rivera	Sales	2023-01-10	NULL

A NULL TerminationDate means the employee is still active today. Now, someone asks you: What was headcount in the Engineering department on the last day of each month in 2023?

Your first instinct might be to write something like:

-- THIS DOESN'T WORK -- shown for illustration
Headcount (Wrong) =
COUNTROWS(
    FILTER(
        Employees,
        Employees[HireDate] <= MAX('Date'[Date])
    )
)

This counts everyone hired up to the selected date — but never subtracts people who left. You add the termination condition:

-- STILL WRONG -- doesn't handle NULLs or date context correctly
Headcount (Still Wrong) =
COUNTROWS(
    FILTER(
        Employees,
        Employees[HireDate] <= MAX('Date'[Date])
            && Employees[TerminationDate] >= MAX('Date'[Date])
    )
)

This breaks in two ways. First, TerminationDate is NULL for active employees, and NULL >= [any date] evaluates to FALSE in DAX — so you'd filter out every current employee. Second, when your visual shows multiple dates (like a monthly trend chart), MAX('Date'[Date]) returns the last date in the entire selection, not each individual date in the visual's rows.

This is the core problem: you need to evaluate "is this employee active?" for each specific date independently, not for the overall filter context. That requires a fundamentally different approach.

Key insight: The Event-in-Progress pattern works by iterating over each date in your date table and, for each date, counting how many intervals from your event table overlap with that specific date. This is the opposite of how most aggregation works — instead of aggregating events up to a date, you're asking each date to look back at the events table.

Building the Model Correctly

Before writing a single measure, you need the right data model. Here's what you need:

1. A Date table — a complete, contiguous calendar with no gaps. This is non-negotiable. If you don't have one, Time Intelligence in DAX: YTD, MTD, Previous Period, and Rolling Averages explains how to create one.
2. Your intervals table — in this case, Employees with HireDate and TerminationDate.
3. Critically: NO direct relationship between Date and Employees on a single date column. This might feel wrong, but it's intentional.

Why no relationship? Because an employee's activity spans a range of dates, not a single date. A standard relationship can only connect one column from one table to one column in another. If you relate Date[Date] to Employees[HireDate], filtering to a specific date only shows employees hired on that exact date — completely useless for our problem. Instead, we'll handle the range logic entirely in the measure.

Note: You might be tempted to model this with a bridge table — a pre-expanded snapshot that has one row per employee per day. This absolutely works and performs better at scale, but it has serious storage costs. A table with 10,000 employees over 5 years would contain ~18 million rows. We'll discuss when to choose that approach in the performance section.

Your model should look like this:

• Date table with a Date column marked as the date table
• Employees table with HireDate, TerminationDate, and dimension columns like Department
• A Department dimension table (optional but clean), related to Employees
• No relationship between Date and Employees on date columns

The Core Event-in-Progress Measure

Here's the fundamental headcount measure:

Headcount =
VAR CurrentDate = MAX('Date'[Date])
RETURN
    COUNTROWS(
        FILTER(
            ALL(Employees),
            Employees[HireDate] <= CurrentDate
                && (
                    ISBLANK(Employees[TerminationDate])
                    || Employees[TerminationDate] >= CurrentDate
                )
        )
    )

Let's walk through every line:

• VAR CurrentDate = MAX('Date'[Date]) — captures the date in the current filter context. When this measure appears in a visual row for "March 31, 2023," CurrentDate is that date. When you have multiple rows, each row evaluates this independently. Using VAR here is important — it captures the value once and uses it consistently inside the expression. DAX Variables in Practice: Using VAR and RETURN to Simplify Complex Measures explains why this matters for avoiding context transition problems.

• ALL(Employees) — this removes any filter context that might be on the Employees table. Without this, if someone filters by department in a slicer, that filter would flow to the FILTER function and we'd lose the ability to count across the full population. We'll add department filtering back explicitly in a moment.

• The two conditions in FILTER:

  • Employees[HireDate] <= CurrentDate — the employee was hired on or before this date
  • ISBLANK(Employees[TerminationDate]) || Employees[TerminationDate] >= CurrentDate — the employee is either still active (NULL termination date) or their termination date is on or after the current date

When you put this measure in a matrix visual with Date[Month] (using the last day of each month) on rows, you'll get an accurate headcount for each month end. That's already powerful. But we can do more.

Adding Dimension Context

The ALL(Employees) in the measure throws away all filters on that table — including department slicers your users will absolutely want. Here's how to restore that correctly:

Headcount with Filters =
VAR CurrentDate = MAX('Date'[Date])
VAR FilteredEmployees =
    CALCULATETABLE(
        Employees,
        ALLEXCEPT(Employees, Employees[Department], Employees[EmployeeType])
    )
RETURN
    COUNTROWS(
        FILTER(
            FilteredEmployees,
            Employees[HireDate] <= CurrentDate
                && (
                    ISBLANK(Employees[TerminationDate])
                    || Employees[TerminationDate] >= CurrentDate
                )
        )
    )

ALLEXCEPT(Employees, Employees[Department], Employees[EmployeeType]) removes all filters on Employees except for the ones on Department and EmployeeType. This lets slicers on those columns flow through while still ignoring any date-related filters that could interfere with the interval logic.

Tip: List every dimension column that should be "slicer-able" in the ALLEXCEPT arguments. If users should be able to filter by location, add it. If they shouldn't see individual employee rows (only aggregated), don't add EmployeeID. Being explicit here prevents surprising behavior when new slicers are added to a report.

A cleaner pattern that scales better as dimension filters grow:

Headcount (Clean) =
VAR CurrentDate = MAX('Date'[Date])
RETURN
    CALCULATE(
        COUNTROWS(
            FILTER(
                Employees,
                Employees[HireDate] <= CurrentDate
                    && (
                        ISBLANK(Employees[TerminationDate])
                        || Employees[TerminationDate] >= CurrentDate
                    )
            )
        ),
        ALL('Date')
    )

Here we use ALL('Date') inside CALCULATE to strip only the date filters from context — leaving all Employees filters (department, type, location, etc.) intact. This is usually the right approach: your interval logic should be immune to date filters (since you're capturing the date as a variable and using it explicitly), but should respect all other dimension filters.

Handling the Range: Period Headcount Averages

A snapshot on a single date is useful, but analysts often want averages over a period — "what was our average headcount during Q1?" This is where the pattern gets genuinely interesting. You need to iterate over each date in a period and average the headcount across those dates:

Average Headcount in Period =
AVERAGEX(
    VALUES('Date'[Date]),
    VAR CurrentDate = 'Date'[Date]
    RETURN
        COUNTROWS(
            FILTER(
                ALL(Employees),
                Employees[HireDate] <= CurrentDate
                    && (
                        ISBLANK(Employees[TerminationDate])
                        || Employees[TerminationDate] >= CurrentDate
                    )
            )
        )
)

AVERAGEX iterates over each date in the current filter context (so if Q1 is selected, it iterates over ~90 dates). For each date, it calculates headcount using the snapshot logic, then averages those 90 numbers. This is the correct way to compute "average headcount during a period" — not by dividing total employee-days by the period length, which is subtly different.

You can do the same with MAXX to find peak headcount, MINX for trough, or SUMX to sum employee-days (useful for calculating FTE-days for capacity planning).

Peak Headcount in Period =
MAXX(
    VALUES('Date'[Date]),
    VAR CurrentDate = 'Date'[Date]
    RETURN
        COUNTROWS(
            FILTER(
                ALL(Employees),
                Employees[HireDate] <= CurrentDate
                    && (
                        ISBLANK(Employees[TerminationDate])
                        || Employees[TerminationDate] >= CurrentDate
                    )
            )
        )
)

Warning: AVERAGEX and MAXX over a date table with many rows can be slow because they trigger the inner COUNTROWS(FILTER(...)) for every single date. If your date table spans 5 years of daily granularity (1,826 dates) and your employees table has 5,000 rows, you're executing roughly 9 million row comparisons per visual cell. We'll cover optimization strategies in the performance section.

Extending to Concurrent Activity: Support Tickets and Reservations

The same pattern applies perfectly to any interval-based dataset. Let's switch to a support ticket scenario. Your Tickets table has:

TicketID	Category	OpenedAt	ClosedAt
T-001	Billing	2024-01-15 08:23	2024-01-15 14:47
T-002	Technical	2024-01-15 09:01	2024-01-17 11:30
T-003	Billing	2024-01-15 10:15	NULL
T-004	Technical	2024-01-16 07:44	2024-01-16 16:00

If you want to count tickets open at the end of each day, the pattern is identical to headcount:

Open Tickets (End of Day) =
VAR CurrentDate = MAX('Date'[Date])
RETURN
    CALCULATE(
        COUNTROWS(
            FILTER(
                Tickets,
                DATEVALUE(Tickets[OpenedAt]) <= CurrentDate
                    && (
                        ISBLANK(Tickets[ClosedAt])
                        || DATEVALUE(Tickets[ClosedAt]) >= CurrentDate
                    )
            )
        ),
        ALL('Date')
    )

But what if you need concurrent activity at hourly granularity? You'll need a time dimension — not just a date table, but a datetime spine. This is a more advanced modeling challenge. If your granularity requirement is hourly, create a calculated table or import a time spine:

-- Calculated table for hourly spine (practical for up to ~1 year)
HourlySpine =
ADDCOLUMNS(
    GENERATESERIES(
        DATEVALUE("2024-01-01"),
        DATEVALUE("2024-12-31") + (23/24),
        1/24  -- step size is 1 hour (1/24 of a day)
    ),
    "HourLabel", FORMAT([Value], "YYYY-MM-DD HH:00")
)

Your concurrent ticket measure then becomes:

Concurrent Open Tickets =
VAR CurrentHour = MAX(HourlySpine[Value])
RETURN
    CALCULATE(
        COUNTROWS(
            FILTER(
                Tickets,
                Tickets[OpenedAt] <= CurrentHour
                    && (
                        ISBLANK(Tickets[ClosedAt])
                        || Tickets[ClosedAt] > CurrentHour
                    )
            )
        ),
        ALL(HourlySpine)
    )

Notice the boundary condition changed from >= to > for the close timestamp. This reflects the semantic: a ticket closed at exactly 14:00 is no longer open at 14:00. Whether you use >= or > depends entirely on your business rules — document this explicitly in your measure comments.

Building a Headcount Variance Measure

Now let's bring in something from the real reporting world: headcount change between two periods. This requires running the Event-in-Progress pattern twice — once for each comparison point — and subtracting.

Headcount Change vs Prior Month =
VAR CurrentPeriodEnd = EOMONTH(MAX('Date'[Date]), 0)
VAR PriorPeriodEnd = EOMONTH(MAX('Date'[Date]), -1)

VAR CurrentHeadcount =
    COUNTROWS(
        FILTER(
            ALL(Employees),
            Employees[HireDate] <= CurrentPeriodEnd
                && (
                    ISBLANK(Employees[TerminationDate])
                    || Employees[TerminationDate] >= CurrentPeriodEnd
                )
        )
    )

VAR PriorHeadcount =
    COUNTROWS(
        FILTER(
            ALL(Employees),
            Employees[HireDate] <= PriorPeriodEnd
                && (
                    ISBLANK(Employees[TerminationDate])
                    || Employees[TerminationDate] >= PriorPeriodEnd
                )
        )
    )

RETURN
    CurrentHeadcount - PriorHeadcount

This structure — using VAR to capture each snapshot independently — is clean, readable, and avoids repeating logic. If you need to show this as a waterfall chart element (joiners minus leavers), you could decompose the change into its components: new hires who started between the two dates, and terminations that occurred between the two dates.

New Hires in Period =
VAR PeriodStart = EOMONTH(MAX('Date'[Date]), -1) + 1
VAR PeriodEnd = EOMONTH(MAX('Date'[Date]), 0)
RETURN
    CALCULATE(
        COUNTROWS(Employees),
        ALL('Date'),
        Employees[HireDate] >= PeriodStart,
        Employees[HireDate] <= PeriodEnd
    )

Leavers in Period =
VAR PeriodStart = EOMONTH(MAX('Date'[Date]), -1) + 1
VAR PeriodEnd = EOMONTH(MAX('Date'[Date]), 0)
RETURN
    CALCULATE(
        COUNTROWS(Employees),
        ALL('Date'),
        Employees[TerminationDate] >= PeriodStart,
        Employees[TerminationDate] <= PeriodEnd,
        NOT ISBLANK(Employees[TerminationDate])
    )

These measures work beautifully together for an HR dashboard: opening headcount + new hires - leavers = closing headcount. This "waterfall" reconciliation is a standard HR reporting requirement, and DAX handles it cleanly once you separate the snapshot logic from the flow logic. For more on building waterfall-style variance measures, see DAX Waterfall Chart Measures: Calculating Bridge Components for Variance Analysis Between Periods, Budgets, and Scenarios.

Multi-Category Snapshots and the SUMMARIZE Pattern

Sometimes you need the Event-in-Progress result broken down by category — for example, headcount per department per day, displayed as a stacked area chart. This works naturally when the measure respects dimension filters, but there's a more powerful pattern when you want to build the breakdown inside a single calculated table for export or further analysis:

-- Calculated table: Headcount snapshot by department and month end
HeadcountSnapshot =
VAR MonthEnds =
    ADDCOLUMNS(
        DISTINCT(EOMONTH('Date'[Date], 0)),
        "MonthEnd", EOMONTH('Date'[Date], 0)
    )

RETURN
GENERATE(
    VALUES(Employees[Department]),
    VAR Dept = Employees[Department]
    RETURN
    ADDCOLUMNS(
        MonthEnds,
        "Headcount",
        COUNTROWS(
            FILTER(
                ALL(Employees),
                Employees[Department] = Dept
                    && Employees[HireDate] <= [MonthEnd]
                    && (
                        ISBLANK(Employees[TerminationDate])
                        || Employees[TerminationDate] >= [MonthEnd]
                    )
            )
        )
    )
)

Warning: Calculated tables like this are computed at refresh time, not query time. That makes them fast to query but means they don't respond to slicers or user filters. Use them for pre-computed snapshots that don't need to be interactive, and use measures for anything that needs to respond to user input.

This connects to a broader pattern of building DAX Virtual Tables in Practice: Using ADDCOLUMNS, SUMMARIZE, and GENERATEALL to Build Complex Aggregations Without Helper Tables — the GENERATE + ADDCOLUMNS combination is one of the most powerful tools in DAX for building complex aggregation structures.

Performance Considerations and When to Pre-Expand

The Event-in-Progress pattern is computationally expensive when applied naively to large datasets. Here's why: for every cell in your visual, DAX has to scan the entire Employees table (or Tickets table) and check every row's interval against the current date. This is an O(n) scan per visual cell, with no index support.

Let's quantify. A report showing monthly headcount for 3 years (36 months) with 5 department slices = 180 visual cells. Each cell scans 50,000 employees = 9 million row evaluations per render. That's manageable. But a daily granularity report with 1,095 days × 20 departments × 50,000 employees = 1.1 billion row evaluations. That is not manageable.

When to use the measure-based pattern:

• Datasets under ~10,000 interval rows
• Monthly or quarterly granularity visuals
• Interactive reports where pre-expansion isn't feasible

When to pre-expand to a snapshot table:

• Large employee populations (>10,000)
• Daily or sub-daily granularity requirements
• The snapshot dimensions are fixed (you know exactly what breakdowns you need)

The pre-expanded approach adds a table to your model — often generated in Power Query or via a Python/SQL process — that has one row per employee per day (or per relevant time period). This trades storage for query performance, which is usually the right trade for large-scale production reports.

Tip: A middle ground is to pre-expand only to month-end snapshots in Power Query. Instead of daily rows, you generate one row per employee per month showing their status as of month end. This reduces a 10,000 employee × 5-year daily table from 18M rows to just 600K rows, while still supporting all standard monthly reporting requirements.

For tuning slow measures and profiling scan costs, Performance Tuning DAX: Optimize Slow Measures with DAX Studio is essential reading — specifically the sections on storage engine vs. formula engine scans.

Handling Edge Cases

Zero-Duration Events

What happens if OpenedAt equals ClosedAt? The event has zero duration. Your <= and >= conditions still handle this correctly — the event is "in progress" at exactly that timestamp. Usually that's fine, but validate with your business stakeholders whether a zero-duration event should count.

Future Start Dates

If someone enters a hire date in the future (a pre-hire record), your measure will include them in headcount for dates after their start date — which may or may not be correct. Add a guard:

Headcount (Strict) =
VAR CurrentDate = MAX('Date'[Date])
RETURN
    COUNTROWS(
        FILTER(
            ALL(Employees),
            Employees[HireDate] <= CurrentDate
                && Employees[HireDate] <= TODAY()  -- exclude pre-hires
                && (
                    ISBLANK(Employees[TerminationDate])
                    || Employees[TerminationDate] >= CurrentDate
                )
        )
    )

Overlapping Intervals for the Same Entity

In some datasets, the same employee might appear twice with overlapping date ranges (re-hires, transfers modeled as separate records). Decide whether to count them as one person or two employment instances. If one person with two records should count as one, you need DISTINCTCOUNT on a unique personal identifier rather than COUNTROWS.

Unique Active Employees =
VAR CurrentDate = MAX('Date'[Date])
RETURN
    CALCULATE(
        DISTINCTCOUNT(Employees[PersonID]),
        FILTER(
            ALL(Employees),
            Employees[HireDate] <= CurrentDate
                && (
                    ISBLANK(Employees[TerminationDate])
                    || Employees[TerminationDate] >= CurrentDate
                )
        )
    )

No Active Intervals (BLANK vs Zero)

When no employees are active (perhaps you're looking at a date before the company existed), COUNTROWS of an empty table returns BLANK, not 0. Wrap with COALESCE if your trend charts need explicit zeros:

Headcount (No Blanks) =
COALESCE(
    [Headcount],
    0
)

Note: Don't suppress BLANKs prematurely. BLANK is semantically meaningful — it means "no data," whereas 0 means "we checked and it was zero." For trend charts, you often want 0. For ratio calculations, you might want BLANK to prevent division-by-zero. Decide per measure.

Hands-On Exercise

Build the following end-to-end in Power BI Desktop:

Dataset setup: Create the following tables manually using "Enter Data":

Employees table:

EmployeeID, Name, Department, HireDate, TerminationDate
1001, Sarah Chen, Engineering, 2022-01-15, 
1002, Marcus Webb, Sales, 2021-06-01, 2023-08-31
1003, Priya Nair, Engineering, 2023-03-20, 
1004, Jordan Lee, HR, 2020-09-01, 2022-12-31
1005, Alex Rivera, Sales, 2023-01-10, 
1006, David Kim, Engineering, 2022-07-01, 2023-06-30
1007, Fatima Hassan, HR, 2023-04-01, 
1008, Tom Bradley, Sales, 2021-01-15, 2022-09-30

Date table (or create one in DAX):

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2024,12,31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMM"),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "MonthEnd", EOMONTH([Date], 0),
    "IsMonthEnd", IF(DAY([Date]) = DAY(EOMONTH([Date], 0)), TRUE, FALSE)
)

Mark the Date table as a date table using the Date column. Do not create a relationship between Date and Employees.

Build these measures in order:

1. Headcount — basic snapshot measure using the pattern in this lesson
2. Headcount (Dept Filter) — version that respects a Department slicer using ALL('Date') inside CALCULATE
3. Average Headcount in Period — using AVERAGEX over the date context
4. Peak Headcount in Period — using MAXX
5. New Hires in Period — employees whose HireDate falls within the selected date range
6. Leavers in Period — employees whose TerminationDate falls within the selected date range

Build these visuals:

1. A line chart with YearMonth on X-axis and Headcount (Dept Filter) on Y-axis, with a Department legend. Add a Department slicer and verify it filters correctly.
2. A matrix with Department on rows, Year on columns, and Average Headcount in Period as values. Verify that Q1 2023 Engineering shows Sarah Chen and Priya Nair (who joined March 20) averaging appropriately.
3. A card showing current headcount (no date filter) — expect 5 (the employees with no TerminationDate, as of today).
4. A table with YearMonth, New Hires in Period, Leavers in Period, and Headcount side by side. Verify that opening headcount + new hires - leavers = closing headcount for each month.

Validation check: For December 31, 2022, Engineering headcount should be 2 (Sarah Chen hired Jan 2022, active; David Kim hired July 2022, active; Jordan Lee is HR not Engineering). For July 1, 2023, Engineering headcount should be 2 (Sarah Chen still active; Priya Nair active since March 2023; David Kim terminated June 30, 2023 — not included).

Common Mistakes & Troubleshooting

Mistake 1: Not using ALL('Date') or ALL(Employees) correctly

Symptom: Headcount shows the same number in every row of a matrix, or shows BLANK for most rows.

Cause: The date filter from the visual is being applied inside the FILTER function, restricting Employees before you can check the interval.

Fix: Ensure you're using ALL('Date') inside CALCULATE or ALL(Employees) inside FILTER to strip the appropriate filters before applying your interval logic manually.

Mistake 2: NULL TerminationDate not handled

Symptom: Current employees are always excluded; only terminated employees appear in headcount.

Cause: Employees[TerminationDate] >= CurrentDate evaluates to BLANK (effectively FALSE) when TerminationDate is NULL.

Fix: Add the ISBLANK(Employees[TerminationDate]) OR condition as shown throughout this lesson. This is the single most common bug in this pattern.

Mistake 3: Using MAX('Date'[Date]) without capturing in a VAR

Symptom: Correct results in simple visuals but wrong results in complex measures that call the date multiple times.

Cause: MAX('Date'[Date]) evaluates in the current filter context each time it's called. Inside nested iterators, this can return different values than expected.

Fix: Always VAR CurrentDate = MAX('Date'[Date]) at the start of the measure and reference CurrentDate throughout. This is especially important in DAX Variables in Practice: Using VAR and RETURN to Simplify Complex Measures.

Mistake 4: Using COUNTROWS on a table with no matching rows returns BLANK, not 0

Symptom: Trend lines have gaps; percentage calculations produce errors.

Fix: Wrap the measure in COALESCE([Measure], 0) in visuals where zero is meaningful.

Mistake 5: Comparing date columns of mismatched types

Symptom: DAX error about type mismatch, or all comparisons return FALSE.

Cause: HireDate might be stored as a text column (especially after "Enter Data" entry) rather than as a Date type.

Fix: In Power Query, ensure date columns are typed as Date (not Text or DateTime). Check the column type indicator in the Power Query editor — it should show a calendar icon.

Mistake 6: Forgetting that AVERAGEX needs at least one row to return a value

Symptom: Average headcount returns BLANK for periods with no dates (e.g., future periods in a partially-filled year).

Fix: Wrap with IF(HASONEVALUE('Date'[Year]) || ..., [Average Headcount in Period], BLANK()) or use COALESCE as appropriate.

Tip: When debugging Event-in-Progress measures, create a simple test visual: a single-column table showing Date[Date], with the Headcount measure alongside it, filtered to just one week. This makes it easy to trace through the logic date by date and spot exactly where the count goes wrong.

Summary & Next Steps

The Event-in-Progress pattern is one of those DAX techniques that unlocks an entire category of analytical questions that simply can't be answered with standard aggregation. The key principles to internalize:

1. Capture the current date as a VAR before entering any iterator or FILTER function
2. Use ALL() strategically to strip date filters while preserving dimension filters
3. Always handle NULL/BLANK end dates — they represent ongoing intervals and are the most common source of bugs
4. Choose the right boundary semantics (>= vs >) based on whether your events are inclusive or exclusive at boundaries
5. Pre-expand to a snapshot table when scale demands it, but measure-based approaches are correct and often fast enough for typical reporting datasets

The pattern extends naturally to many business domains:

• Finance: loan balances in-force, policies active, subscriptions current
• Healthcare: patients admitted, treatments in progress
• IT/Ops: incidents open, deployments in flight, licenses consumed
• Real estate/hospitality: rooms occupied, leases active

Your next area to explore is cohort analysis — understanding how groups defined at one point in time behave over subsequent periods. The DAX Cohort Analysis: Building Retention, Churn, and Lifetime Value Measures with GENERATE and Date-Based Segmentation lesson builds directly on the interval-checking fundamentals you've learned here.

For financial reporting scenarios where you need semi-additive measures (inventory levels, bank balances) that overlap with this pattern conceptually, DAX for Semi-Additive Measures: Solving Opening Balance, Closing Balance, and Inventory Calculations with LASTNONBLANK and FIRSTNONBLANK is a logical companion. And when your reports grow complex enough that render performance becomes a concern, invest time in Performance Tuning DAX: Optimize Slow Measures with DAX Studio to profile exactly where your formula engine is spending its time.

The Event-in-Progress pattern is deceptively simple in structure but requires precise thinking about context, NULLs, and boundaries. Once it clicks, you'll find yourself reaching for it constantly — and your users will wonder how they ever got along without it.