How to create a Time Table in Power BI in a few simple steps

In a previous blogpost, we already explained how to make a date table in Power BI with DAX. Besides the dates you can add extra properties for each date: year, month-names, weekday-names,… But what about hours and minutes? In this blog and video, we’ll show you how to create time tables in Power BI

Many reports contain dates and therefore chances are you will end up creating one or more date tables in your Power BI model. Such a date table contains at least all the dates in a date range that correspond to your business needs (there is no need to start your date table on January 1, 1930 if your business transactions started in 1990). Besides the dates you will probably add extra properties for each date: year, month-names, weekday-names, … Something like this:

You can look at this blogpost of Frederik Vandeputte as it seamlessly explains how to create such a date table with DAX.

What about hours and minutes

Sometimes you need to report on a more granular level than the day. Maybe you work in a package delivery company and received the request to report on an hourly basis, looking for patterns on the time of delivery. Or maybe as a scientist you do an iteration of experiments and even the seconds are important in a specific setup. And finally, what if you want to report on everything in between, like 15-minute intervals ([0-14], [15-29], [30-44], [45-59]).

A first approach might be to add the hours/minutes/seconds to your date-table. But you will soon find out that this will generate an enormous amount of data (31.536.000 rows for every non-leap year). Also, from a modelling perspective, you will find out that it is better to separate the date and time dimensions. So far, so good, but how do we create such a time table in Power BI.

It is not so difficult when we use the correct functions. The first one is GENERATESERIES and produces a table with a single column containing consecutive values between a given range. If we want to create a range with all possible hours, it can be done with this DAX formula (calculated table):

GENERATESERIES (0; 23)

Because the column is always named [Value], we need to rename it. If we don’t, the upcoming cross-join will not work when we pass the same column name more than once. Therefore, we rename it with the SELECTCOLUMNS function:

SELECTCOLUMNS(GENERATESERIES(0; 23); "Hour"; [Value])

Now let’s put this in a variable `HourTable` and use the same logic for minutes and seconds (both ranging from 0 to 59). That way, we end up with 3 variables that contain a single-column table:

VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0; 23); "Hour"; [Value])
VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0; 59); "Minute"; [Value])
VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0; 59); "Second"; [Value])

Having these 3 tables, you just need to cross-join them, and add an extra column that holds the time (you need this column to join with your fact table):

ADDCOLUMNS(
CROSSJOIN(HourTable; MinuteTable; SecondsTable);
"Time"; TIME([Hour]; [Minute]; [Second])
)

The resulting table (after changing the data type to Time) contains 86.400 rows and looks like this:

If seconds are too granular, you can leave them out (notice the `0` in the Time function):

ADDCOLUMNS(
CROSSJOIN(HourTable; MinuteTable);
"Time"; TIME([Hour]; [Minute]; 0)
)

And in case you do not need more granularity then let’s say 15 minutes (00:15, 00:30, …, 23:30, 23:45) you can change the definition of MinuteTable (notice the 3^rd parameter in the GENERATESERIES function):

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0; 45; 15); "Minute"; [Value])

One last thing: you can add extra columns to create extra levels of grouping:

ADDCOLUMNS (
CROSSJOIN(HourTable; MinuteTable);
"Time"; TIME([Hour]; [Minute]; 0);
"Half Hour Block"; IF([Minute] < 30; "0-29"; "30-59")
)

Power BI Model

After your time table is ready, you can link it with a fact table. In order to do so, you need to have the time in your fact table as a separate column and link it with the time column in the time table. In this example you see that the fact table `Fitbit2019` is linked with both a date and a time table:

Full Code

For easy copy & paste purposes (depending on your system settings, you might need to swap `;` with `,`):

TimeTable =

VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0); (23)); "Hour"; [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0); (59)); "Minute"; [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0); (59)); "Second"; [Value])

 RETURN
 
ADDCOLUMNS(

CROSSJOIN(HourTable; MinuteTable; SecondsTable);

"Time"; TIME([Hour]; [Minute]; [Second])

)

Do you want to see a step-by-step video? Just watch our tutorial.