How to find total duration of time in power bi?

Question

I have time format in text as a table in Power bi.

I want to find total duration of time by adding all time together in days, hours, minutes, seconds Something like this:-

How can I write Dax for this?

Thanks in advance!!

Answer 1

total_duration_dax_2 = 

var total_second = 
SUMX(
    TIME2,
    INT(
        VALUE(LEFT(TIME2[Table2],2))
        
    )
) * 60 * 60
+
SUMX(
    TIME2,
    INT(
        VALUE(MID(TIME2[Table2],4,2))
    )
) * 60
+
SUMX(
    TIME2,
    INT(
        VALUE(RIGHT(TIME2[Table2],2))
        
    )
)

var DayCount = INT(total_second/(24*60*60))
var HoursCount = MOD(INT(total_second/(60*60)),24)
var MinCount   = MOD(INT(total_second/60),60)
var SecCount   = MOD(total_second,60)

RETURN DayCount & " Days & " & HoursCount & " Hours & " & MinCount & " Minutes & " & SecCount & " Seconds"

The result is same

Answer 2

Although you already excepted my first answer, I wants to let you know about another option. I am posting it as separate answer as I do not wants to mess between two answer.

You can also do the whole thing using DAX without steps I have explained in my earlier answer. You can do it directly from list of value like-

The DAX query is(bit big but you can make a try)-

total_seconds_dax = 

var total_second = 
SUMX(
    total_time,
    INT(
        MID(
            total_time[Time],
            1,
            SEARCH(":",total_time[Time],1,0) - 1
        )
    )
) * 60 * 60
+
SUMX(
    total_time,
    INT(
        MID(
            total_time[Time],
            SEARCH(":",total_time[Time],1,0) + 1,
            SEARCH(
                ":",
                total_time[Time],
                SEARCH(":",total_time[Time],1,0) + 1
               ,0
            ) 
            - 
            (SEARCH(":",total_time[Time],1,0) + 1)
        )
    )
) * 60
+
SUMX(
    total_time,
    INT(
        MID(
            total_time[Time],
            SEARCH(
                ":",
                total_time[Time],
                SEARCH(":",total_time[Time],1,0) + 1
                ,0
            ) + 1 
            ,
            LEN(total_time[Time]) - 
            SEARCH(
                ":",
                total_time[Time],
                SEARCH(":",total_time[Time],1,0) + 1
                ,0
            )
        )
    )
)

var DayCount = INT(total_second/(24*60*60))
var HoursCount = MOD(INT(total_second/(60*60)),24)
var MinCount   = MOD(INT(total_second/60),60)
var SecCount   = MOD(total_second,60)

RETURN DayCount & " Days & " & HoursCount & " Hours & " & MinCount & " Minutes & " & SecCount & " Seconds"

Answer 3

If your input values are representing "HH:mm:ss" format, you can perform following steps to achieve your required output.

Step-1: After loading your data, duplicate your "time" column and then split the column as shown in the below image using delimiter ":"

The output of above action will be as below. Please rename column names as Hour, Minute and Second. Also change data type of those columns as number/whole number.

Step-2: Add a custom column with script-

total_second = 
([Hour] * 60 * 60)
+ ([Minute] *60)
+ ([Second])

The output will be as below with total seconds per row-

Step-3: Go back to report by applying the changes.

Step-4: Create a Measure as below-

custom_second_to_day = 

var DayCount = INT(total_time[m_total_second]/(24*60*60))
var HoursCount = MOD(INT(total_time[m_total_second]/(60*60)),24)
var MinCount   = MOD(INT(total_time[m_total_second]/60),60)
var SecCount   = MOD(total_time[m_total_second],60)

RETURN DayCount & " Days & " & HoursCount & " Hours & " & MinCount & " Minutes & " & SecCount & " Seconds"

The final output of the Measure will be - "2 Days & 21 Hours & 56 Minutes & 51 Seconds"