Add the extra time of an appointment to the to the next hour of the time slot.

[Anonymous]

Hello Experts,
I have some demo data about the doctor's (many) appointments at a hospital.
That looks like this:

S. No.	Appointent ID	Start Time	Time_Slot	Duration (in mins)
1	A1	12:15	12:00	37
2	A2	8:34	8:00	12
3	A3	9:57	9:00	43
4	A4	10:11	10:00	69
5	A5	9:33	9:00	56
6	A6	11:45	11:00	95
7	A7	11:00	11:00	30
8	A8	12:48	12:00	88
9	A9	10:35	10:00	30

(Time_Slot column is displaying the Period of the hour when the appiontment started. e.g. 8:00 for 8:34, 12:00 for 12:33.)

# Issue:

(Notice Appoinrments_ID A4, A5, A6, A8 here)

Appointent_ID	Start_Time	8:00	9:00	10:00	11:00	12:00	13:00	14:00	Duration_Total
A1	12:15					37			37
A2	8:34	12							12
A3	9:57		43						43
A4	10:11			69					69
A5	9:33		56						56
A6	11:45				95				95
A7	11:00				30				30
A8	12:48					88			88
A9	10:35			30					30

The matrix is displaying that-
* Appointment_ID "A4" has taken duration 69 minutes in the 10:00 hr's slot by starting_time 10:11 hrs. 
        But the fact is that out of total 69 minutes duration of this A4 appointment, only 49 minutes have been utilized in the period of 10:00 hr time_slot, and remaining 20 minutes of appointment have occured in period of 11:00 hr timeslot.
        (10:00 hr actually starts from 10 hours: 00 minutes: 00 second and ends at 10 hours: 00 minutes: 59 seconds. And then starts the 11th hour (i.e. 11 hours: 00 minutes: 00 second) 

* Similarly, in Appointment_ID A8, how is it possible to consume 88 minutes in an hour period? atleast 28 minutes must have been utilized in next hour (If the appointment has started sharp at 11:00:00 hrs). Right? That's the point.
I hope, you got the logic...

******************************************************
So, the desired output should display 49  minutes for A4 appointment (as the 10th hour end at this duration and 11th hour starts), and add the remaining 20 minutes to the 11:00 hour slot.

See the desired output here: 

(Notice Appoinrments_ID A4, A5, A6, A8 now)

Appointent_ID	Start Time	8:00	9:00	10:00	11:00	12:00	13:00	14:00	Duration_Total
A1	12:15					37			37
A2	8:34	12							12
A3	9:57		3	40					43
A4	10:11			49	20				69
A5	9:33		27	29					56
A6	11:45				15	60	20		95
A7	11:00				30				30
A8	12:48					12	60	16	88
A9	10:35			25	5				30

I an not sure whether a single measure will work or we will need to create Calc. Column!

Your opinions and solutions will be appreciated.

Thanks in advance.

Kind Regards
🙂

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can create a  new calculated table to expand and calculate the rolling durations:

Expand = 
VAR _duration =
    GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 0, 0 ), TIME ( 1, 0, 0 ) )
VAR _expand =
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( 'Table', _duration ),
            VAR _start =
                HOUR ( [Start Time] )
            VAR _end =
                HOUR ( [Start Time] + TIME ( 0, [Duration (in mins)], 0 ) )
            RETURN
                [Value] >= TIME ( HOUR ( [Start Time] ), 0, 0 )
                    && [Value]
                        <= ( [Start Time] + TIME ( 0, [Duration (in mins)], 0 ) )
        ),
        "S. No.", [S. No.],
        "Appointent ID", [Appointent ID],
        "Start Time", [Start Time],
        "New_Time_slot", [Value],
        "Total Duration", [Duration (in mins)],
        "Current Duration",
            VAR sTime = [Start Time]
            VAR cTime = [Value]
            RETURN
                IF (
                    HOUR ( cTime ) = HOUR ( sTime ),
                    MIN ( [Duration (in mins)], ( 60 - MINUTE ( sTime ) ) ),
                    MIN (
                        60,
                        [Duration (in mins)]
                            - HOUR ( cTime - sTime ) * 60
                            - MINUTE ( cTime - sTime )
                    )
                )
    )
RETURN
    _expand

Regards,
Xiaoxin Sheng