A duration is not the same as datetime!

Here you will find the pbix.

From time to time, the same question pops up at community.powerbi.com: How can durations be aggregated?

What this means is shown in the following picture

There is a simple table containing three rows of sample data:

The question to be answered is: How long do the three events take?

When we are looking at something like this: 2:00

We think, at least most of the people I know, an event that takes 2 hours.

But when we change the notation just a little to 2:00 AM, we think: Whoa, that’s early (or late, depending on the perspective, of course).

The first notation represents a duration, whereas the 2nd notation represents a specific point in time. So the difference seems to be small but is huge. I’m neglecting important information like the timezone and an accurate date for simplicity.

In this article, I’m talking about duration, meaning measuring the duration of an event, like how long it takes to travel from point A to point B, reading a book, or assembling your latest Lego set.

It’s important to remember that the Power BI dataset does not know a data type duration. The following article lists all the data types Power BI can handle at the time of this writing (26th of December, 2021):: Data types in Power BI Desktop

The above article states that Power BI Desktop does know the datatype duration, but it’s Power Query that has some means to handle data of the data type duration and not the Power BI dataset.

As the Power BI dataset does not know the data type duration, the solution presented in this article is a combination of Power Query and DAX. I’m using Power Query to transform the datetime values into integer values representing the number of seconds passed since the beginning of timekeeping (according to Power Query). In this solution, it’s necessary to remember that a datetime or date data type is stored as a decimal. The decimal part (right from the decimal point) reflects the fraction of a day and the integer part (left from the decimal point) reflects the number of days.

These are the steps for the column startDateTime using Power Query

1. Duplicate the column startDateTime (to keep the original column)
2. Rename the column accordingly
3. Change the data type to decimal
4. Change the data type to duration
5. Transform the column to TotalSeconds

I repeated the steps for the column endDateTime accordingly, and finally, I created a new column that I call duration- Total seconds.

The following picture shows the resulting table:

Finally, the DAX measure “duration” does the rest:

var _TotalSeconds = CALCULATE( SUM( 'Duration - sample data'[duration - TotalSeconds] ) )

var noSecondS = 60

var noSecondsPerHour = noSecondS * 60

var noSecondsPerDay = noSecondsPerHour * 24

return

if( NOT( ISBLANK( _TotalSeconds ) )

    ,var _Days = TRUNC(DIVIDE(_TotalSeconds , noSecondsPerDay ) )

    var RemainingSecondsFromDay = MOD( _TotalSeconds , noSecondsPerDay )

    var _Hours = TRUNC(DIVIDE( RemainingSecondsFromDay , noSecondsPerHour ) )

    var RemaingSecondsFromHour = MOD( RemainingSecondsFromDay , noSecondsPerHour )

    var _Minutes = TRUNC(DIVIDE( RemaingSecondsFromHour , noSecondS ) )

    var RemainingSecodndsFromHour = MOD( RemaingSecondsFromHour , noSecondS )

    return

    IF( _Days = 0

        , _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"

        , _Days & " days " & _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"

    )

    ,BLANK()

)

Transforming the data type datetime into a decimal representing seconds allows all the aggregations and calculations I can imagine. Just the “reformatting” is a little wordy.

Of course, as the measure returns a string, this measure does not work inside the values bucket of visuals.

Hopefully, this will be handy!