Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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:
@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
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |