Add Duration to the last appointment of the day

[Anonymous]

Hello,

I'm trying to calculate the number of hours worked on a date from a Doctors template.  For example, on 4/3 Doctor A had scheduled appointments from 8-3:30.  The measure I'm using now calculates this to 7 hours 30 minutes however, the last appointment was at 3:30 and was 15 minutes in duration.  In h:mm the duration of the worked day should be 7:45.  

The table is structured as following:

appt schdlng prvdr apptdate apptstarttime apptslotduration

Doctor A	4/3/2023	8:00 AM	15
Doctor A	4/3/2023	8:15 AM	15
Doctor A	4/3/2023	8:30 AM	30
Doctor A	4/3/2023	9:15 AM	15
Doctor A	4/3/2023	9:30 AM	30
Doctor A	4/3/2023	9:30 AM	15
Doctor A	4/3/2023	10:00 AM	15
Doctor A	4/3/2023	10:15 AM	15
Doctor A	4/3/2023	10:30 AM	30
Doctor A	4/3/2023	11:00 AM	15
Doctor A	4/3/2023	11:15 AM	15
Doctor A	4/3/2023	1:00 PM	30
Doctor A	4/3/2023	2:00 PM	15
Doctor A	4/3/2023	2:00 PM	15
Doctor A	4/3/2023	2:15 PM	15
Doctor A	4/3/2023	2:30 PM	30
Doctor A	4/3/2023	2:45 PM	15
Doctor A	4/3/2023	3:00 PM	15
Doctor A	4/3/2023	3:15 PM	15
Doctor A	4/3/2023	3:30 PM	15

the measures I've tried:

• First Appt = min(Sheet1[apptstarttime])
• last appt time = max(Sheet1[apptstarttime])

• Scheduled Day = [First Appt]-[last appt time]

The report resembles:

In summary, on 4/3 the Doctor's first appointment was at 8am and the last at 3:30 however the duration of the 3:30 appoinment was 15 minutes.  I need the last appt time measure to include the duration so it says 3:45 and then the Scheduled day to be 7:45.  A better name for this measure might be "End Time - Last Appointment".

Any help is appreciated.

Thank you!

[Anonymous]

@FreemanZ 

This worked perfectly for the sample I prepared for the community but for my business case, it seems to be adding 60 minutes to every last appointment for each Doctor.  Is is possilbe it's returning the MAX appt duration across all Doctors for each appointment date?  I think I may have solved the issue, see below.

This is the result I get from my real case:

appt schdlng prvdr	apptdate	First Appt	Last Appt	Scheduled Day
Doctor A	4/3/2023 0:00	8:00:00	3:30:00	8:30:00

I tried removing the ALL in the line above and I think that gives me what I want since I believe the ALL ingores the context imparted by the provider attribute (is that the correct theory?  I'm still learning DAX).

Thanks @FreemanZ , I'll mark this as solved.

[FreemanZ · Accepted solution]

hi @Anonymous 

not sure if i fully get you, try like:

Scheduled Day = 
VAR _lastappttime = [last appt time]
VAR _lastduration =
MAXX(
    FILTER(
        ALL(Sheet1),
       Sheet1[apptstarttime]=_lastappttime
    ),
    Sheet1[apptslotduration]
)
RETURN
[last appt time] -[First Appt] + _lastduration/24/60

it worked like: