DAX: Count at a point in time

22 Dec, 2025

The code and techniques in this post was aided by AI

Here I have a situation where I have a table events. The table contains two date columns: one when the event was started, and then another when it was finished. It's connected to a date table calendar, where event[startdate] is connected to calendar[date].

I want to know, at any point in time (and at any time scale, e.g. per day, or per month, or per year), how many events were active at that time, regardless of the state of the event right now.

Let's draw up an example table.

Let's say that today is 2025-12-22. Right now, there is only one active event - the one that hasn't finished yet. If the date in question is in the past, our code needs to be able to pretend that some of these events haven't finished yet.

If we're standing on 2025-06-18, we should only see one active event, Event B. If we're standing on 2025-06-30, we should see both Event B and Event C. If were standing on 2025-07-28, we should again see two events, Event A and Event C.

So how do we do this?

First of all, somewhere, we will need to have

ALL(event)

This is because we don't want to traverse the relationship between event and calendar. We have to force the code to inspect the entire table.

We also have to tell the code to look inside the time context boundaries. When we display this on the page, we will be using calendar[date] as the X-axis, so we need to set boundaries relative to this column.

VAR peqriodStart = MIN('calendar'[date])
VAR periodEnd = MAX('calendar'[date])

To see which ones were active in any given time period:

Count of Active (Point in Time) = 
VAR periodStart = MIN('calendar'[date])
VAR periodEnd = MAX('calendar'[date])

RETURN
CALCULATE(
    COUNTX(event, [event]),
    FILTER(
        ALL(event), 
        event[startdate] <= periodEnd  // event started before or as this period ends
        && periodEnd < event[enddate] // event ends strictly after this period ends
    )
)

#dax #powerbi