cancel
Showing results for
Did you mean:
Member

## Help with average of time calculations

Hi All, I have been struggling with creating an average time in a MAtrix for some time now. In my raw data I first created a lengh of stay for a patient. So my new raw date field column subtracting a discharge date from a admit date which are in  Date/Time format. The end result does return the correct time in HH:MM. When I place this filed into my matrix, it does correctly provide a sum, but I can not figue out how to get it to return an average.

I have tried different formula and formatting with no luck. The quick calc only has "earliest, latest and counts.

Any suggestion would be greatlt apprecaited

Thanks

Terry

3 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator

## Re: Help with average of time calculations

@tbobolz

The AVERAGE in DAX is only support a numeric type. To get an expected out, you can follow

`average time = TIME(INT(AVERAGE('Table'[elapsed seconds])/3600),INT(MOD(AVERAGE('Table'[elapsed seconds]),3600)/60),0)`

Member

## Re: Help with average of time calculations

Thanks, I have not worked with DateDiff before and learnt something knew today. This seems pretty straight forward yet I am getting the below error. Seems obvious my start date should always be less than my end date, but with 1.5 m rows I guess it is possible to have an error. I'll have to check our software and see if it will allow a mistake such as this. If it does happen, I would think it would still calculate all the proper rows. But is this not the case.

Terry

New Contributor

## Re: Help with average of time calculations

If you feel that you won't be able to clean the data at the source or transform it upon load, you can always use IF logic to prevent the error:

```Hours in the ED =
IF (
'ED Cycle Time Data'[ER Arrival DateTime]
> 'ED Cycle Time Data'[ER Departure DateTime],
DATEDIFF ( [...depart...], [...arrival...], HOUR ),
DATEDIFF ( [...arrival...], [...depart...], HOUR ))```
6 REPLIES 6
Super Contributor

## Re: Help with average of time calculations

Hi @tbobolz,

Can you please post a small sample of data to help us build something.

Cheers,

Phil

Proud to be a Datanaut!

Member

## Re: Help with average of time calculations

Sure, thanks for taking a look at this.

The below picture displays the fields utilized. The calculation of "Hours in the ED" is calculating fine in the data here.

However, I am not sure how to present it in an average of "Hours in the ED" in the Matrix. I have tried other claculation and measure with no success.

Thanks for the assistance.

Terry

Moderator

## Re: Help with average of time calculations

@tbobolz

The AVERAGE in DAX is only support a numeric type. To get an expected out, you can follow

`average time = TIME(INT(AVERAGE('Table'[elapsed seconds])/3600),INT(MOD(AVERAGE('Table'[elapsed seconds]),3600)/60),0)`

Member

## Re: Help with average of time calculations

Thanks, I have not worked with DateDiff before and learnt something knew today. This seems pretty straight forward yet I am getting the below error. Seems obvious my start date should always be less than my end date, but with 1.5 m rows I guess it is possible to have an error. I'll have to check our software and see if it will allow a mistake such as this. If it does happen, I would think it would still calculate all the proper rows. But is this not the case.

Terry

New Contributor

## Re: Help with average of time calculations

If you feel that you won't be able to clean the data at the source or transform it upon load, you can always use IF logic to prevent the error:

```Hours in the ED =
IF (
'ED Cycle Time Data'[ER Arrival DateTime]
> 'ED Cycle Time Data'[ER Departure DateTime],
DATEDIFF ( [...depart...], [...arrival...], HOUR ),
DATEDIFF ( [...arrival...], [...depart...], HOUR ))```
Member

## Re: Help with average of time calculations

Perfect! I get caught up in learning DAX and need to step back and think more dynamically, I should have easily figured that out.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 240 members 2,387 guests
Recent signins: