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

# How do I calculate average turn around time for particular column with different date/time formats??

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

10-08-2018 11:20 PM - edited 10-08-2018 11:57 PM

I want to find average turnaround time for this column..This column has different formats (ie)3 days 18:42:00 and 5 mons 3days 15:42:00 ....my doubt is how can we change the differrent formats into same format(eg: as Minutes) and find averageTurn Around time and return with date format ..Any Idea??.

Solved! Go to Solution.

Accepted Solutions

## Re: How do I calculate average turn around time for particular column with different date/time forma

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

10-09-2018 12:49 AM

Some one may have better solution, but you can try the following way to split your datetime.

First, in Power Query Editor, choose the original time column, and then select "Split Column by Delimiter" -> "Space" + "Right-most delimiter".

This step is used to get the time value.

After that, save and apply the changes.

Create two measures

First one is used to retrive the Month value, use the following DAX

Month = IF ( IFERROR ( FIND ( "mons", 'Time'[Time.1] ), 0 ) > 0, LEFT ( 'Time'[Time.1], IFERROR ( FIND ( "mons", 'Time'[Time.1] ), 0 ) - 1 ), "0" )

Second on is used to retrive the Day value, use the following DAX:

Day = IF ( IFERROR ( SEARCH ( "days", 'Time'[Time.1] ), 0 ) > 0, IF ( IFERROR ( SEARCH ( "mons", 'Time'[Time.1] ), 0 ) > 0, MID ( 'Time'[Time.1], IFERROR ( SEARCH ( "mons", 'Time'[Time.1] ), 0 ) + 5, LEN ( 'Time'[Time.1] ) - ( IFERROR ( SEARCH ( "mons", 'Time'[Time.1] ), 0 ) + 5 ) - 4 ), MID ( 'Time'[Time.1], 1, LEN ( 'Time'[Time.1] ) - 5 ) ), "0" )

After that, you should get the Month + Day + Time value. You can convert them into seconds for further calcuation

All Replies

## Re: How do I calculate average turn around time for particular column with different date/time forma

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

10-09-2018 12:49 AM

Some one may have better solution, but you can try the following way to split your datetime.

First, in Power Query Editor, choose the original time column, and then select "Split Column by Delimiter" -> "Space" + "Right-most delimiter".

This step is used to get the time value.

After that, save and apply the changes.

Create two measures

First one is used to retrive the Month value, use the following DAX

Month = IF ( IFERROR ( FIND ( "mons", 'Time'[Time.1] ), 0 ) > 0, LEFT ( 'Time'[Time.1], IFERROR ( FIND ( "mons", 'Time'[Time.1] ), 0 ) - 1 ), "0" )

Second on is used to retrive the Day value, use the following DAX:

Day = IF ( IFERROR ( SEARCH ( "days", 'Time'[Time.1] ), 0 ) > 0, IF ( IFERROR ( SEARCH ( "mons", 'Time'[Time.1] ), 0 ) > 0, MID ( 'Time'[Time.1], IFERROR ( SEARCH ( "mons", 'Time'[Time.1] ), 0 ) + 5, LEN ( 'Time'[Time.1] ) - ( IFERROR ( SEARCH ( "mons", 'Time'[Time.1] ), 0 ) + 5 ) - 4 ), MID ( 'Time'[Time.1], 1, LEN ( 'Time'[Time.1] ) - 5 ) ), "0" )

After that, you should get the Month + Day + Time value. You can convert them into seconds for further calcuation

## Re: How do I calculate average turn around time for particular column with different date/time forma

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

10-09-2018 01:19 AM - edited 10-09-2018 01:26 AM

thanks for ur answer and other doubt is how can i return average calculation in date format For eg:total avg turnaround time in minutes will be=96690...and i want as 2 mons 7days 3:40:00