Max Events in Progress Today

[Anonymous]

Hello PBI Community, 

I'm looking for some help on an events in progress problem.

Currently, I have a single fact table that has a Start and End column that includes date and time down to the second.

I am trying to find a way to find the max number of concurrent sessions on a given day. 

For example:
    Start            End

1. 3/22 10:32   3/22 10:50

2. 3/22 10:44   3/22 10:56

3. 3/22 4:05     3/22 4:20

4. 3/22 4:10     3/22 4:15

5. 3/22 4:12     3/22 4:30

The answer here for this date of 3/22 would be 3 because that's the highest number of concurrent sessions going on.

That's my ultimate goal, but I still am having trouble figuring out events in progress based on time. Any hints?

[AlB · Accepted solution]

Hi @Anonymous 

Try this as calculated column in the table you show. Note I've named the first column 'ID'. You'll have to add the year to the dates in the Start and End columns and convert them to DateTime type.

You can then easily extract the maximum from the new calculated column.

Concurrent with this session =
COUNTROWS (
    FILTER (
        Table1;
        Table1[ID] <> EARLIER ( Table1[ID] )
            && NOT ( Table1[End] < EARLIER ( Table1[Start] ) || Table1[Start] > EARLIER ( Table1[End] ) )
    )
) + 1

[Anonymous]

Fantastic, works like a charm. Really appreciate it!