How to calcualte average of time columns in Power BI (DAX)

I work with Power Bi since the first realse in 2015 and since that time, working with the time datatyp in Power BI has always been akward.

Some time ago I stumbeld over the problem of calculating an average of a time columns. Why ? A customer wants to analyse the average time of sales dynamically calcualted over a given hierachy of products and stores. Even worse in my case we were taking about a SQL Server Direct Query connection (huge database several billions of rows). In theory that should be easy, but it turns out you need some tricks. I will show you in this blog post how you can handle this kind of problem.

First things first, Power BI like many tools is storing date and time information as very long decimal number. For example:

Table showing time to decimal conversion in Power BI

If the datatype of a column is datetime, then 1 is translated 31.12.1899 00:00:00. See the following Screenshot for more examples

Datetime datatyp in Power BI

Back to our initial problem. Even so Power BI stores time information as a floating point number it is not capable of doing mathematical aggreagtion on this kind of columns. That means no sum, mean, max, min.

Default aggregations in Power BI

If you use for example the AVERAGE DAX function Power BI will show you the average as floating point number. This can be a workaround for users that understand how time is stored in Power BI. For everybody else 0,61 is not a time.

Simple DAX measure for average over time

So how can we solve this ?

We can use the TIME() function for a workaround. TIME will turn a hour, minute and second into a usable time object in Power BI. We can now do some calculation to translate our average decimal time value (0,1) to extract hours, minutes and seconds. Let’s see how it looks like in DAX measure:

Float Time DAX Import = 
// If your table is in import mode you can use AVERAGE over a time column.
// For SQL Server time datatype column this will not work. I will show you later.
var avg_time = AVERAGE(Zeit_Import[Time_Column])

var hour_decimal =   avg_time*24 // Calculate the hour value in decimal
var hour_full = int(hour_decimal) // int is like round down
var minute_decimal = (hour_decimal-hour_full)*60 // calc. minutes as decimal
var minute_full =   int(minute_decimal) // extract minutes as int
var seconds = (minute_decimal-minute_full)*60 // get seconds
return
// Use TIME function to bring all together
TIME(hour_full,minute_full,seconds)

Afterwards you have to change the formating in the Measure tools. In theory you can use FORMAT (FORMAT([Your Measure], "h:mm:ss")) but this will turn your measure in a text, which can be a problem if you use it in charts.

Average on a time column

Change format of the new measure

The INT alternative

You can also use the whole number datatype (INT) to represent seconds since 00:00:00 as numbers. So 23:59:59 would be 86399 (60*60*24–1). This might look a little bit more like a timestamp. In theory the compression of this kind of int numbers should be more efficient, even both (decimal and whole number) use 8 byte of storage.

Int_Time_Dax = 

var avg_int_time = AVERAGE(Zeit_DQ[Int_Time_Column])
VAR Hours = INT (avg_int_time / 3600) 
VAR Minutes = INT ( MOD( avg_int_time - ( Hours * 3600 ),3600 ) / 60) 
VAR Seconds = ROUNDUP(MOD ( MOD( avg_int_time - ( Hours * 3600 ),3600 ), 60 ),0)
return 
    TIME(Hours,Minutes,Seconds)

Let’s talk about Direct Query

If you are using a direct query dataset with a SQL Server in the background this approach will not work out of the box. The problem is that the SQL Server can’t average a time column. This problem exists with all time related datatypes (time, datetime, datetime2, smalldatetime).

No avg on Time Column in T-SQL

Technical you can write a AVERAGE([Time_Column])in DAX but you will get weird errors.

Changing the data type of the column to decimal in Power BI Query Editor will not work. The only way to solve this, is to create a new column in your table or view that represents the time information as a decimal floating point number between 0 and 1. To convert a time column to a float you can use the following pattern:

CONVERT(float,convert(datetime,convert(varchar, [YOUR TIME OR DATETIME COLUMN], 8))) as Float_Time_Column

The new column can be used in Power BI to calculate the average.

Conclusion:

After working with Power BI for such a long time, sometimes I’m still confused that something simple like calculations with time is not already solved. But remember in the beginning Power BI wasn’t capable to change the colors of bar charts. So we are on the right way!

If you are interest in more of my Power BI content have a look at the ORAYLIS Blog