Reply
Regular Visitor
Posts: 21
Registered: ‎11-03-2016
Accepted Solution

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

 


Accepted Solutions
Moderator
Posts: 3,051
Registered: ‎03-06-2016

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

 

Capture.PNG

 

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

Capture.PNG

View solution in original post

Regular Visitor
Posts: 21
Registered: ‎11-03-2016

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.

 

Thanks again for your reply, much appreciated!

 

Terry

 

pic 3.jpg

View solution in original post

New Contributor
Posts: 450
Registered: ‎08-25-2016

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 )
)

View solution in original post


All Replies
Super User
Posts: 2,618
Registered: ‎11-29-2015

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Regular Visitor
Posts: 21
Registered: ‎11-03-2016

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.

 

pic 1.jpg

 

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.

pic 2.jpg

 

Thanks for the assistance.

 

Terry

 

 

Moderator
Posts: 3,051
Registered: ‎03-06-2016

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

 

Capture.PNG

 

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

Capture.PNG

Regular Visitor
Posts: 21
Registered: ‎11-03-2016

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.

 

Thanks again for your reply, much appreciated!

 

Terry

 

pic 3.jpg

New Contributor
Posts: 450
Registered: ‎08-25-2016

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 )
)
Regular Visitor
Posts: 21
Registered: ‎11-03-2016

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.

 

Your assistance is much appreciated!