Median Of A Time Column

DAX DAX Calculations
1

Hi, I’m trying to get the Median of a Time Column and coming up empty. I work in Health Care and we are trying to compute the Median Discharge Time for all Discharges that happen before 11am. I know how to use DAX to do the “Before 11” part, but how do I get the Median of a Date?

In Qlikview where I used to do most of my development, Dates are also numbers so it’s really easy to do. However in PBI, I get the error that the MEDIAN function can’t be done for dates.

Any help here would be appreciated!

1 Like
2

I think I might have figured it out. Do you see anything wrong with this or is there a better way perhaps?

MEDIAN_TA_TIME =

Var Med = MEDIAN('Turn Around Time'[TA_TIME_Hours])

VAR Hours = INT(Med)

VAR Minutes = (RIGHT(Med,2)/ 100) * 60

RETURN

Hours &  ":" &  Minutes
1 Like
3

Looks optimal to me. Nice one.

2 Likes
4

@mcnater, this was an interesting one for me coming from a VB/VBA/.NET programming background, where date/times were actually stored in a numeric format and only displayed in date/time format. I can expect with my data to need to calculate metrics involving duration, time to complete, etc.

The following article about the TIME() function confirms in the Remarks section that Excel also stores date/time values as numbers but DAX/Power BI does not: https://docs.microsoft.com/en-us/dax/time-function-dax. It also indicates you could use the TIME() function to return your median as an actual time value if desired, such as to use as an axis value on a visual.

A very cool issue and very good to know the result that worked for you!

Enterprise%20DNA%20Expert%20-%20Small