Help with average of time calculations

[tbobolz]

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

[Eric_Zhang · Accepted solution]

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

[tbobolz · Accepted solution]

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

[Phil_Seamark]

Hi @tbobolz,

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

Cheers,

Phil

[tbobolz]

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

[Eric_Zhang · Accepted solution]

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

[tbobolz · Accepted solution]

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

[dedelman_clng · Accepted solution]

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

[tbobolz]

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!