powerbidaxpowerquerypowerbi-desktop

Change Duration Column from Text Type to Number/Time Data Type

I am using the below formula to calculate the duration in HH:MM:SS formula.

However, I am unable to update this new column to Number/Time data type as the colons ":" are necessary in the Text type, but would not be necessary if the type was Time.

I need to sum this result and as you are aware im sure, the text column types does not allow this.

Adjusted Duration HHMMSS = 
VAR _hrs = QUOTIENT ( Total[Average Duration Adjusted] , 60 )
VAR _mins = INT (Total[Average Duration Adjusted] - _hrs * 60 )
VAR _sec = MOD ( Total[Average Duration Adjusted] , 1.0 ) * 60
RETURN
FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")&":"&FORMAT(_sec,"00")

Source column :

enter image description here

Adjusted duration :

enter image description here

Please help!

Should I include a TIMEVALUE line in my formula?

Thank you!!

I attempted to update the column type to whole numbers and removing the colons but this does not work as expected.

I would expect this column to be a time column so it can be summed not "first" or counted.

Solution

  • Instead of formatting the result as HH:MM:SS, try to calculate the total duration in seconds:

    Adjusted Duration Total Seconds = 
VAR _hrs = QUOTIENT(Total[Average Duration Adjusted], 60)
VAR _mins = INT(Total[Average Duration Adjusted] - _hrs * 60)
VAR _sec = MOD(Total[Average Duration Adjusted], 1.0) * 60
RETURN
_hrs * 3600 + _mins * 60 + _sec

    Then you can convert it back to time format :

    Adjusted Duration HHMMSS = 
TIME(
    QUOTIENT([Adjusted Duration Total Seconds], 3600), 
    MOD(QUOTIENT([Adjusted Duration Total Seconds], 60), 60), 
    MOD([Adjusted Duration Total Seconds], 60)
)

    enter image description here

    Update :

    You can use the CC Adjusted Duration Total Seconds to create your measure :

    Total Adjusted Duration in Seconds = SUM('Total'[Adjusted Duration Total Seconds])

    and another measure to convert the summed seconds back into HH:MM:SS format:

    Total Adjusted Duration HHMMSS = 
VAR _TotalSeconds = [Total Adjusted Duration in Seconds]
VAR _hrs = QUOTIENT(_TotalSeconds, 3600)
VAR _mins = QUOTIENT(MOD(_TotalSeconds, 3600), 60)
VAR _secs = MOD(_TotalSeconds, 60)
RETURN
FORMAT(_hrs, "00") & ":" & FORMAT(_mins, "00") & ":" & FORMAT(_secs, "00")