- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-31-2017 03:25 PM

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

Solved! Go to Solution.

Accepted Solutions

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-05-2017 03:10 AM

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)

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2017 12:08 PM

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.

Thanks again for your reply, much appreciated!

Terry

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2017 12:32 PM

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 )

)

All Replies

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-31-2017 04:14 PM

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2017 06:47 AM

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

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-05-2017 03:10 AM

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)

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2017 12:08 PM

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.

Thanks again for your reply, much appreciated!

Terry

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2017 12:32 PM

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 )

)

## Re: Help with average of time calculations

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-06-2017 02:01 PM

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

Your assistance is much appreciated!