Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |