Average of first time value per day

[tbobolz]

Hi all,

I struggling with a formaula to calculate the average start time of surgeries each day. 

I have a column "Surgery_Start_DT_TM"  (7/11/2019 7:58:00 AM) This column list all surgery start date and time as the title suggest.

I need to find the first time value for each day, then add each first time values for those days and divid by the number of days.

This would indicated the average time our first surgery begun each day. Then as I slice by surgeon, we can see each surgeons results.

I can't seem to pull this one out of my head.

Thanks for any help you can offer.

[Anonymous · Accepted solution]

@tbobolz  - 

Here is an example table that only contains 9 days (July 1-9 2019). For each day, the hour is set to the same as the day number. So, each day only has one time, and the average time of those is 5AM:

DateTime = 
var cal = CALENDAR(date(2019,7,1), date(2019,7,9))
var add_DateTime =  ADDCOLUMNS(cal, "DateTime", [Date] + (1/24*day([date])))
var add_DateOnly = ADDCOLUMNS(add_DateTime, "DateOnly", DATEVALUE([DateTime])) --Bold part could be a Calculated Column formula
var add_TimeOnly = ADDCOLUMNS(add_DateOnly, "TimeOnly", TIMEVALUE([DateTime])) --Bold part could be a Calculated Column formula
return add_TimeOnly

Then, the measure groups by day, finds the minimum time for each day, finds the average of the minimums, and formats as time:

Average Start Time = 
var minTimes = SUMMARIZE('Date',[DateOnly],"Minimum Time", MIN('Date'[TimeOnly]))
return FORMAT(AVERAGEX(minTimes, [Minimum Time]), "HH:mm:SS")

Hope this helps,

Nathan

[Anonymous · Accepted solution]

@tbobolz  - 

Here is an example table that only contains 9 days (July 1-9 2019). For each day, the hour is set to the same as the day number. So, each day only has one time, and the average time of those is 5AM:

DateTime = 
var cal = CALENDAR(date(2019,7,1), date(2019,7,9))
var add_DateTime =  ADDCOLUMNS(cal, "DateTime", [Date] + (1/24*day([date])))
var add_DateOnly = ADDCOLUMNS(add_DateTime, "DateOnly", DATEVALUE([DateTime])) --Bold part could be a Calculated Column formula
var add_TimeOnly = ADDCOLUMNS(add_DateOnly, "TimeOnly", TIMEVALUE([DateTime])) --Bold part could be a Calculated Column formula
return add_TimeOnly

Then, the measure groups by day, finds the minimum time for each day, finds the average of the minimums, and formats as time:

Average Start Time = 
var minTimes = SUMMARIZE('Date',[DateOnly],"Minimum Time", MIN('Date'[TimeOnly]))
return FORMAT(AVERAGEX(minTimes, [Minimum Time]), "HH:mm:SS")

Hope this helps,

Nathan

[tbobolz · Accepted solution]

Wow, I can't believe how long it ahs been since this posted, I just got back to this thought and below is the solution to what I was originally seeking. Much simplier then I thought, or I'm gettign a little better at DAX! 

Thanks again .

This calculation was straight off my date and time columns:

Avg Surgery Start = FORMAT(AVERAGEX(

    KEEPFILTERS(VALUES('Surgery Case Data'[Service Date - Date])),

    CALCULATE(MIN('Surgery Case Data'[Surgery Start Time]))

),"H:MM AM/PM")

[tbobolz]

Thank you for you time Nathan!!  

It is much appreciated. This looks perfect! Let me play around with this next week and I'll let you know how it goes. Of  course this projects on hold for yet a different fire.  LOL!

Terry