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

[Gokul]

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

[Anonymous · Accepted solution]

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

[Gokul]

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