The “Rich Cousin” of dCalendar: Discover the Power of the Time Dimension in Power BI

Everyone who works with Power BI has heard of (or even built) a calendar dimension table — the famous dCalendar. It's almost a celebrity among analysts: it's the foundation for time-based analysis, groupings by year, month, quarter, semester... basically everything that involves time in days.

But let me ask you a question:

👉 What if, instead of analyzing when on the calendar something happened, we wanted to understand at what hour of the day it usually happens?

Exactly. Few people talk about it, but there is a concept just as powerful as dCalendar: the time dimension — or simply dTime. It's dCalendar's “rich cousin” (or maybe the mysterious cousin 😄).

💡 Understanding the context: fact and dimension tables

First of all, let's recall the classic structure of data models in Power BI:

• Fact tables hold your business events or transactions — for example sales, service calls, missions, orders, etc.
• Dimension tables bring the context to analyze those events — such as the calendar dimension, product dimension, customer dimension or our new friend, the time dimension.

The magic happens when we connect the dimensions to the fact tables. That is what lets you build powerful, dynamic analyses — and understand not only the “what”, but also the “when” and the “how” of something happening.

⏳ Why create a time dimension?

In many analyses, the hour can reveal patterns that the day alone doesn't show.

📊 Examples:

• At what time do most service calls or system failures occur?
• Which part of the day concentrates the largest volume of sales?
• When is the “prime time” of your operations?

These insights only appear when you look at time in detail — not just at the date.

⚙️ How to create a time dimension in Power BI

The first step is simple: start from a table that has an hour (or datetime) column. If it is a datetime, it's a good practice to split it into two:

• One containing only the date
• Another containing only the time

This makes analyses, relationships and DAX calculations much easier.

🧩 This split can be done in two ways:

🧮 In Power Query (Query Editor)

You can create custom columns using M, for example:

DateColumn = DateTime.Date([DateTime])
TimeColumn = DateTime.Time([DateTime])

This extracts the date and time parts right at transformation time — ensuring performance and clarity in the model.

🪄 Or with visual transformations (in Power BI Desktop itself)

Just select the datetime column and create new calculated columns:

For the date:

Date = DATEVALUE(Table[DateTime])

For the time:

Time = TIMEVALUE(Table[DateTime])

Both approaches are valid — choose according to your need and project stage.

🧠 Building dTime with DAX

After splitting the columns, you can create your time dimension table with the formula below:

dTime =
FILTER(
    VALUES(space_missions[Time]),
    NOT(ISBLANK(space_missions[Time]))
)

This expression collects all the unique time values from your data (which can be a fact table, for example) and removes the null values.

💪 Enriching your time table

From there, you can enrich dTime with new columns that help with categorization and analysis, following these suggestions:

TimeText = CONVERT(dTime[Time], STRING)

🔹 Converts the time value to formatted text (e.g. “07:30:00”).

TimeNumber = CONVERT(MID(dTime[Time],1,2),INTEGER)

🔹 Extracts the numeric hour, enabling slicers and calculations (e.g. 7, 14, 19).

Interval =
SWITCH(
    TRUE(),
    dTime[TimeNumber] >= 0  && dTime[TimeNumber] <  6 , "⏱️00:00 - 06:00",
    dTime[TimeNumber] >= 6  && dTime[TimeNumber] < 12 , "⏱️06:00 - 12:00",
    dTime[TimeNumber] >= 12 && dTime[TimeNumber] < 18 , "⏱️12:00 - 18:00",
    "⏱️18:00 - 00:00"
)

🔹 Classifies each time into a specific part of the day — perfect for visualizing the concentration or volume of occurrences.

🚀 The power of details

Just as dCalendar helps us understand the “when” at the macro level, dTime lets us understand the “when” at the micro level. And very often, that is where the real insight lives.

So, the next time you build your Power BI model, remember:

📆 dCalendar is indispensable…

🕒 But dTime can be revealing.

Had you heard of this possibility before? Have you used a time dimension in your reports?