Forum Discussion

saud968's avatar
saud968
Memorable Member
2 years ago
Solved

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 

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 

 



General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago

     

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

     

     

18 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    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"

    • saud968's avatar
      saud968
      Memorable Member
      2 years ago

      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's avatar
      saud968
      Memorable Member
      2 years ago

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

      • Ahmedx's avatar
        Ahmedx
        Super User
        2 years ago

        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 ) ) )
    )