What is a time dimension?
A time dimension is like a date dimension except you use it to categorise time throughout a day. For example, you could use it to categorise when tickets arrive for a service desk. The grain of a time dimension is generally one row for every minute of the day.
The columns you could include are:
- Time (e.g. 00:01, 00:02)
- Time 15 Min Group (e.g. 00:00, 00:15)
Creating a date dimension is a trivial task, you can use the CALENDAR function to create a table which has every date between two values. For example, if you had date column [Created Date] in the table [Products] then you could use the CALENDAR function to create a date dimension like this:
Step 1: Press "New Table"
Step 2: Type in the formula:
Created Date = CALENDAR(min('Products'[Created Date]), max('Products'[Created Date]))
Step 3: Rename the column "Date" to "Created Date"
You now have a table which has every date between the first created date and the last created date in your products table. You can now create formulas to categorise that date various ways. I'm not going to go into detail about that because it's covered extensively in other blogs by other Power BI users.
The problem with creating a time dimension
The issue is there is no equivalent to CALENDAR for times. But there is a little trick to making it work! When you use the CALENDAR function it creates a column "Date" but you can actually use that column to create the time dimension.
Step 1: Use the calendar function to create multiple rows (one for each minute of the day)
Time Dim = CALENDAR(1, 1440)
Surprisingly this works! The reason it works is that a date value in Power BI is simply a number representing the number of days since the 31st December 1899.
Step 2: Change the format of the column to whole number. This will give you a table with the values 1 to 1440 in the rows.
Step 3: Change the name of the column to Row ID. This number is going to help you construct the time in the next step. You'll also hide it in report view.
Step 4: Create a new column "Created Time" with the formula:
Created Time = TIME(0,'Time'[Row ID]-1,0)
This formula is using the minute parameter of the time function to populate each row of the time dimension.
That's it for the base of the time dimension. From here you can go on and add columns to categorise the times. For example, if you wanted to group the times by 15 minute intervals you could use:
Created 15 Min Group Start Time = time(hour('Created Time'[Created Time]),0,0) + time(0,if(minute('Created Time'[Created Time])>=15&&minute('Created Time'[Created Time])<30,15,if(minute('Created Time'[Created Time])>=30&&minute('Created Time'[Created Time])<45,30,if(minute('Created Time'[Created Time])>=45&&minute('Created Time'[Created Time])<=59,45,0))),0)
The resulting table looks like this:
You can now join this back to a time value in your fact table and use it to categorise the values by 15 min intervals.
Of course you could always import a spreadsheet with time values in it but where's the fun in that!