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

# Average of difference in hh:mm format

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

06-23-2018 01:40 PM

Hi All,

I have 2 date fields where I am calculating difference between them like below

datediff(Replen_stats[create_date_time],Replen_stats[mod_date_time],minute)

Now if it has 5 rows for same so I want to calculate average of minutes for 5 rows in hh:mm format

say sum of minutes for 5 rows is 670 minutes then the evrage should be 670/5=134 minures is average which should show 02:14

Any ideas please

Thanks

## Re: Average of difference in hh:mm format

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

06-23-2018 04:10 PM

Hi @srlabhe123

You could capture the entire calculation in a measure and avoid the DATEDIFF calculation:

Average Time Difference = AVERAGEX ( Replen_stats, Replen_stats[mod_date_time] - Replen_stats[create_date_time] ) + TIME ( 0, 0, 0 )

(The TIME ( 0, 0, 0 ) is there to ensure the measure is cast as a time value.)

Then ensure the Measure Format is HH:mm.

Regards,

Owen

## Re: Average of difference in hh:mm format

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

06-25-2018 07:10 AM

This is working of the diffrenec betwenn dates is less than 24 Hrs but if dates are like below see the value of measure above in first colum.

Its just taking difference between time stamps and not dates.

## Re: Average of difference in hh:mm format

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

06-25-2018 07:10 AM

This is working of the diffrenec betwenn dates is less than 24 Hrs but if dates are like below see the value of measure above in first colum.

Its just taking difference between time stamps and not dates.

## Re: Average of difference in hh:mm format

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

06-25-2018 07:19 AM

You could use this to caluclate the hours and then use datediff to calculate the Days and then build a text measure that displays Days*24+HOUR(Average Time Difference)&":"&MINUTE(Average Time Difference)

## Re: Average of difference in hh:mm format

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

06-25-2018 07:42 AM

R=This too is not working as Days from ha sto be column and not measure. And then the calculation is shwoing wrong

Days*24+HOUR(Average Time Difference)&":"&MINUTE(Average Time Difference)

## Re: Average of difference in hh:mm format

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

06-25-2018 07:50 AM

What if you do something similar to "Average Time Difference" called "Average Days Difference" where you add + Date(0,0,0) to at the end and use [Average Days Difference]*24?

## Re: Average of difference in hh:mm format

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

06-25-2018 07:53 AM

No that wont help me to display as I want to have the difference in hh:mm format only and not in days

## Re: Average of difference in hh:mm format

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

06-25-2018 08:01 AM

Have two Measures [Average Time Difference] and [Average Day Difference] then a third Measure [Hour Difference] to display the result.

Hour Difference = [Average Day Difference]*24+HOUR([Hour Difference])&":"&MINUTE([Hour Difference])

## Re: Average of difference in hh:mm format

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

06-25-2018 11:34 AM

what would be formula for Avg Day, Avg Time difference then?