cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tbobolz Member
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 Eric_Zhang
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

 

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

tbobolz Member
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.

 

Thanks again for your reply, much appreciated!

 

Terry

 

pic 3.jpg

View solution in original post

dedelman_clng New Contributor
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 )
)

View solution in original post

6 REPLIES 6
Phil_Seamark Super Contributor
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


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

Proud to be a Datanaut!

tbobolz Member
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.

 

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

 

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

tbobolz Member
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.

 

Thanks again for your reply, much appreciated!

 

Terry

 

pic 3.jpg

View solution in original post

dedelman_clng New Contributor
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 )
)

View solution in original post

tbobolz Member
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.

 

Your assistance is much appreciated!

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 240 members 2,387 guests
Please welcome our newest community members: