Maximum number of people enrolled between the start and end times per days.

[umekuro]

Hi!

I have below table.

Date StartTime EndTime
a 2021/1/1 8:00 8:30
b 2021/1/1 8:10 8:40
c 2021/1/1 8:20 8:50
d 2021/1/1 8:50 9:00
a 2021/1/2 8:10 8:30
b 2021/1/2 8:00 8:20
c 2021/1/2 8:30 8:50
d 2021/1/2 8:50 9:00

I want to get the maximum number of people enrolled at same time between the start and end times per days.

 2021/1/1      3

 2021/1/2      2

How can I get this rusult in PowerBI?

Thank you in advance.

[umekuro]

Thank you very much for your help!

I didn't explain myself well enough,

so the DAX you showed me was not what I was looking for.

I have modified the sample table.
In your DAX it will be 3, but what I want is 2.
I want to find the maximum number of people who are enrolled at the same time, overlapping,

as shown in the following figure.
Thanks again for your advice!

Name date StartTime EndTime
a 2021/1/1 8:00 8:20
b 2021/1/1 8:20 8:40
c 2021/1/1 8:10 8:30
d 2021/1/1 8:50 9:00
a 2021/1/2 8:10 8:30
b 2021/1/2 8:00 8:20
c 2021/1/2 8:30 8:50
d 2021/1/2 8:50 9:00

[Ashish_Mathur]

Hi,

Will the minutes portion of the start time always be a multiple of :10 or can it be something like 8:13 as well?

[umekuro]

@Ashish_Mathur 

The original data is in minutes, but it would have been great if the count could be rounded to 10 minutes.
I managed to solve that problem by myself.
Thank you very much.

[jdbuchanan71 · Accepted solution]

Ah, interesting, sorry about that.   I was just lucky that my first version happen to give the expected result.

I had to go a bit further and add a time table.  This table holds a row for each minute in a 24 hour period and it is made using this DAX code.

Time Table = 
VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23), "Hour", [Value])
VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value])
RETURN
ADDCOLUMNS(
    CROSSJOIN(HourTable,MinuteTable),
    "Time",Time([Hour],[Minute],0)
)

I can then use the time table to check each minute and see how many enrollments are active in that minute then get the highest value from that calculation.

Enrollments = 
MAXX (
    'Time Table',
    COUNTROWS (
        FILTER (
            Enrollments,
            Enrollments[Start Time] <= 'Time Table'[Time]
                && Enrollments[End Time] > 'Time Table'[Time]
        )
    )
)

I extended the dataset a bit to give it more of a test with overlapping times.

So you can see, in the 8:20 time slot there are 10 people enrolled on 2020/12/31

Using the new measure I get my expected results for 12/31 as well as the correct results for your 2021/1/1 and 2021/1/2 data. 

I have attached my sample file for you to look at.

[umekuro]

@jdbuchanan71 

Thanks to your advice, I managed to get the answer I was looking for.
I am very grateful to you.
Thank you very much.

[jdbuchanan71]

@umekuro 

I think this will give you what you are looking for.

Enrollments = 
MAXX(
    Enrollments,
    VAR _ThisEndTime = Enrollments[End Time]
    VAR _ThisStartTime = Enrollments[Start Time]
    RETURN
    CALCULATE(
        COUNTROWS(Enrollments),
        ALLEXCEPT(Enrollments,Enrollments[Date]),
        Enrollments[Start Time] < _ThisEndTime,
        Enrollments[End Time] > _ThisStartTime
    )
)

At least is works in my test file: