Reply
Member
Posts: 96
Registered: ‎08-29-2018
Accepted Solution

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

[ Edited ]

issue3.png

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??.  

 


Accepted Solutions
Highlighted
Established Member
Posts: 161
Registered: ‎01-02-2018

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

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".

 

2018-10-09_15-43-24.jpg

 

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

 

2018-10-09_15-48-45.jpg

View solution in original post


All Replies
Highlighted
Established Member
Posts: 161
Registered: ‎01-02-2018

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

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".

 

2018-10-09_15-43-24.jpg

 

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

 

2018-10-09_15-48-45.jpg

Member
Posts: 96
Registered: ‎08-29-2018

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

[ Edited ]

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