Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
saud968
Responsive Resident
Responsive Resident

Text to Time format

Hi, 

I have a calculated column in text type - 

Total Hours =
    VAR StartDateTime = support__cw_ops_manage_case_merge_temp[DATE_ENTERED_SR_EST]
    VAR EndDateTime = support__cw_ops_manage_case_merge_temp[DATE_CLOSED_UTC_SR]
    VAR DurationInHours = (EndDateTime - StartDateTime) * 24
    VAR Hours = TRUNC(DurationInHours)
    VAR Minutes = TRUNC((DurationInHours - Hours) * 60)
    VAR Seconds = ROUNDUP((DurationInHours - Hours - Minutes / 60) * 3600, 0)
    RETURN
        FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")
 
Now the reason for the conversion time is that I have another calculated column 
FormattedAHT =
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = IFERROR(VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos)), 0)
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = IFERROR(VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos)), 0)
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")

I have to now subtract total hours minus the formatted AHT. I have converted the formatted AHT column in TIME hh:mm: ss format but the Total hour's column is getting an error 

saud968_0-1701323048479.png

For example in the first dates in the image, the total hours is 169:11:52 it should be minus 1:01:55 which should give output around 168:09:57 approx. but due to a format issue, I am unable to get the result.  Please review the file for more information - https://drive.google.com/file/d/1ua-mfEICRQDFBd7xkPMl_3H1lblT-c7Q/view?usp=drivesdk

 

@lbendlin @Ahmedx 

 



2 ACCEPTED SOLUTIONS
saud968
Responsive Resident
Responsive Resident

 

Total Hours = 
    VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST])*24
    var hr= ROUNDDOWN(h,0)
    var m = (h-hr)*60
    var mn = ROUNDDOWN(m,0)
    var s= rounddown((m-mn)*60,0)
    return format(hr,"00:") & format(mn,"00:") & format (s,"00")
Diff = 
var t = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([AHT Text],"h","|"),"m","|"),"s","")
var d = if(PATHLENGTH(t)=3,value(pathitem(t,1)+DIVIDE(value(pathitem(t,2)),60)+DIVIDE(value(pathitem(t,3)),60*60),DIVIDE(value(pathitem(t,1)),60)+DIVIDE(value(pathitem(t,2)),60*60))
VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST])*24-d
var hr= ROUNDDOWN(h,0)
var m = (h-hr)*60
var mn = ROUNDDOWN(m,0)
var s= rounddown((m-mn)*60,0)
return format(hr,"00:") & format(mn,"00:") & format (s,"00")

 

 

View solution in original post

18 REPLIES 18
Ahmedx
Super User
Super User

I'm not sure that I fully understood what you need, but I created several calculated columns and one measure, I really hope this will help you
the logic is like this:
you need to convert everything into seconds, then calculate it and then convert it back to "hh:mm:ss"

Screenshot_2.pngScreenshot_3.pngScreenshot_4.pngScreenshot_5.pngScreenshot_6.png

saud968
Responsive Resident
Responsive Resident

@Ahmedx how do i fix the issue with my version of Power BI, please guide

you need to open it on another computer where the latest version of power bi should be

final = 
   VAR _Duration = SUM('support__cw_ops_manage_case_merge_temp'[Total Hoursxx])- SUM(support__cw_ops_manage_case_merge_temp[timtext_To_Time 2])
VAR _Hours = INT(_Duration/3600)
VAR Minutes =
    INT ( MOD( _Duration - ( _Hours * 3600 ),3600 ) / 60)
    VAR Seconds =
    ROUNDUP(MOD ( MOD( _Duration - ( _Hours * 3600 ),3600 ), 60 ),0)
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF (ISBLANK(_Hours), "00",
      IF(   LEN (  _Hours ) = 1,
        CONCATENATE ( "0", _Hours ),
        CONCATENATE ( "", _Hours )
      ))
// Minutes with leading zeros
VAR M =
    IF ( ISBLANK(Minutes), "00",
        IF(LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    ))
// Seconds with leading zeros
VAR S =
    IF ( ISBLANK(Seconds), "00",
       IF( LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    ))
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN

    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )
saud968
Responsive Resident
Responsive Resident

Thi s is the latest version - Version: 2.123.742.0 64-bit (November 2023)

Screenshot_4.png

saud968
Responsive Resident
Responsive Resident

I have the same version and your sample file it is working perfectly but not working in my OG file

I don't know how to help you

saud968
Responsive Resident
Responsive Resident

Hi @Ahmedx I used your previously helped dax. 

1. I created the below format 

Total Hours3 =
    VAR StartDateTime = support__cw_ops_manage_case_merge_temp[DATE_ENTERED_SR_EST]
    VAR EndDateTime = support__cw_ops_manage_case_merge_temp[DATE_CLOSED_UTC_SR]
    VAR DurationInHours = (EndDateTime - StartDateTime) * 24
    VAR Hours = TRUNC(DurationInHours)
    VAR Minutes = TRUNC((DurationInHours - Hours) * 60)
    VAR Seconds = ROUNDUP((DurationInHours - Hours - Minutes / 60) * 3600, 0)
    RETURN
        FORMAT(Hours, "0") & "h " & FORMAT(Minutes, "0") & "m " & FORMAT(Seconds, "0") & "s"


2. then tried your Dax you helped for FormattedAHT
FormattedAHT =
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = IFERROR(VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos)), 0)
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = IFERROR(VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos)), 0)
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")

I had to change it a little as below

FormattedAHT =
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 2)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 3, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = IFERROR(VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos)), 0)
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 3, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = IFERROR(VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos)), 0)
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")
With the changes above i get output for 169h 11m 52s as 16:01:02 please help me figure out to get the complete number

saud968
Responsive Resident
Responsive Resident

No worries, you have helped a lot. I will figure something out and will let you know how I was able to get it done. Give me some time.

Thank you so much for your help 😍

saud968
Responsive Resident
Responsive Resident

@Ahmedx As you can see the Final (Dax) that you helped with does not give the correct output it also it is in text format. Request you to please check the file one more time if possible, Please 🙂

saud968_0-1702294210705.png

 

saud968
Responsive Resident
Responsive Resident

This is great, to let you know what i want is the difference hours between FormattedAHT and Total Hours in hh:mm:ss format. The file you shared the dax does not work on my version of power BI - Version: 2.123.742.0 64-bit (November 2023)

saud968
Responsive Resident
Responsive Resident

@Ahmedx Please review the https://drive.google.com/file/d/1ua-mfEICRQDFBd7xkPMl_3H1lblT-c7Q/view?usp=drivesdk and help me out with yet another issue

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

saud968
Responsive Resident
Responsive Resident

 

Total Hours = 
    VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST])*24
    var hr= ROUNDDOWN(h,0)
    var m = (h-hr)*60
    var mn = ROUNDDOWN(m,0)
    var s= rounddown((m-mn)*60,0)
    return format(hr,"00:") & format(mn,"00:") & format (s,"00")
Diff = 
var t = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([AHT Text],"h","|"),"m","|"),"s","")
var d = if(PATHLENGTH(t)=3,value(pathitem(t,1)+DIVIDE(value(pathitem(t,2)),60)+DIVIDE(value(pathitem(t,3)),60*60),DIVIDE(value(pathitem(t,1)),60)+DIVIDE(value(pathitem(t,2)),60*60))
VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST])*24-d
var hr= ROUNDDOWN(h,0)
var m = (h-hr)*60
var mn = ROUNDDOWN(m,0)
var s= rounddown((m-mn)*60,0)
return format(hr,"00:") & format(mn,"00:") & format (s,"00")

 

 

saud968
Responsive Resident
Responsive Resident

Diff = var t = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([AHT Text],"h","|"),"m","|"),"s","")
var d = if(PATHLENGTH(t)=3,DIVIDE(value(pathitem(t,1)),24)+DIVIDE(value(pathitem(t,2)),24*60)+DIVIDE(value(pathitem(t,3)),24*60*60),DIVIDE(value(pathitem(t,1)),24*60)+DIVIDE(value(pathitem(t,2)),24*60*60))
VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST]-d)*24
var hr= ROUNDDOWN(h,0)
var m = (h-hr)*60
var mn = ROUNDDOWN(m,0)
var s= rounddown((m-mn)*60,0)
return format(hr,"00:") & format(mn,"00:") & format (s,"00")  

I am unable to convert this is time (Data Type) format the reason for this is I need to get an average for the time. getting the below please assist

saud968_0-1702559667630.png

 

That is a data quality issue.  Your End date is before the Start date by two seconds.

saud968
Responsive Resident
Responsive Resident

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.