How to Group Time Intervals in Power BI

Wrangling timestamp data in Power BI can feel like trying to read a story written in minuscule font. When you have sales, user activity, or support tickets logged down to the exact second, spotting larger trends is nearly impossible. This article will show you how to group those overly specific timestamps into meaningful intervals - like morning, afternoon, and evening - using a few straightforward methods right within Power BI.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Bother Grouping Time Intervals?

Before jumping into the "how," let's quickly cover the "why." Your raw data might look something like this: 2023-10-26 09:17:42 AM. While precise, this level of detail isn't very useful for high-level analysis. You can't easily answer important questions like:

• What time of day do we get the most website traffic?
• Are sales higher during business hours or after hours?
• When do our support tickets spike during the day?

By grouping timestamps into broader categories, you can smooth out the noise and reveal the patterns hidden within your data. Instead of analyzing hundreds of individual minutes, you can analyze a handful of simple time blocks. This makes your reports cleaner, your visuals more intuitive, and your insights much easier to find.

Getting Started: A Quick Data Check

First things first, make sure Power BI recognizes your time data correctly. For these methods to work, the column you want to group should be a Date/Time or Time data type.

You can check and change this in the Power Query Editor:

1. Click on Transform data from the Home ribbon to open the Power Query Editor.
2. Select your query on the left.
3. Find your timestamp column. Click the icon next to the column header (it might look like a calendar, clock, ABC, or 123).
4. From the dropdown menu, select Date/Time.

Once your data is in the right format, you’re ready to start grouping.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: The Quick and Easy Way with a Conditional Column

The simplest way to create time groups is by using a Conditional Column in the Power Query Editor. This method is intuitive, requires no code, and is perfect for creating static, well-defined categories.

Let's say we want to group our time data into "Morning," "Afternoon," "Evening," and "Night."

Step 1: Extract the Hour

To group by time of day, we first need a column that just contains the hour.

1. With your query open in the Power Query Editor, select your main timestamp column (e.g., Order Date).
2. Go to the Add Column tab.
3. In the "From Date & Time" section, click Time and then select Hour.
4. You’ll see a new column appear containing only the hour (0-23) from your original timestamp.

Step 2: Create the Conditional Column

Now we'll use our new "Hour" column to build the time groups.

1. Stay in the Add Column tab and click on Conditional Column.
2. A dialog box will appear. This is where you'll define the rules for your groups.
3. Let's name our new column "Time of Day."
4. Now, build your logic. Here is a common setup:

• If the Hour column is greater than or equal to 5 and less than 12, then output Morning.
• Click Add clause.
• If the Hour column is greater than or equal to 12 and less than 17, then output Afternoon.
• Click Add clause.
• If the Hour column is greater than or equal to 17 and less than 21, then output Evening.
• The final Else field, enter Night. This will catch all hours from midnight to 5 a.m. and late hours from 9 p.m. to midnight.

1. Click OK.

You now have a clean, easy-to-use "Time of Day" column! Click Close & Apply in the top-left to return to your report view.

Method 2: The Flexible Approach with a DAX Calculated Column

Sometimes you need more flexibility or want to create time groups without going back into the Power Query Editor. This is where DAX (Data Analysis Expressions) comes in handy. Creating a calculated column with DAX lets you define your groups right in the Report or Data view.

This approach achieves the same result as the conditional column but uses a formula instead of a menu-driven interface.

Step 1: Create a New Calculated Column

1. Navigate to the Data view by clicking the table icon on the left-hand pane.
2. Select the table containing your timestamp data.
3. In the Table tools ribbon that appears at the top, click New column.
4. The formula bar will appear. This is where you'll enter your DAX expression.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Write the DAX Formula

Let's create the same "Time of Day" groups using DAX. Copy and paste the following formula into the formula bar. Be sure to replace 'YourTable'[YourTimestampColumn] with the actual name of your table and timestamp column.

Time of Day (DAX) = 
SWITCH(
    TRUE(),
    HOUR('YourTable'[YourTimestampColumn]) >= 5 && HOUR('YourTable'[YourTimestampColumn]) < 12, "Morning",
    HOUR('YourTable'[YourTimestampColumn]) >= 12 && HOUR('YourTable'[YourTimestampColumn]) < 17, "Afternoon",
    HOUR('YourTable'[YourTimestampColumn]) >= 17 && HOUR('YourTable'[YourTimestampColumn]) < 21, "Evening",
    "Night"
)

Let's break down this formula:

• Time of Day (DAX) =: This is simply the name of our new column.
• SWITCH(TRUE(), ...): A powerful DAX function that checks a series of conditions and returns a value for the first true condition.
• HOUR(...): Extracts the hour (0-23) from your timestamp.
• ... >= 5 && ... < 12: Defines the morning hours.
• "Morning": The value returned if the condition is true.
• The other conditions similarly define Afternoon, Evening, and Night.

Press Enter, and your new calculated column will appear in your table.

Advanced Grouping: Creating 15- or 30-Minute Intervals

What if you need more granular groupings? For example, a customer service manager might want to see how many support calls come in every 30 minutes. Manually creating these bins with conditional columns would be tedious. This is another area where DAX excels.

We can use a formula involving the MROUND function to "round" our timestamps to the nearest interval we want.

First, create a new calculated column as described above. Then, use this DAX formula to create 30-minute time slots. Remember to replace the placeholder with your actual table and column name.

Time Slot (30 min) = 
VAR CurrentTime = 'YourTable'[YourTimestampColumn]
VAR HourPart = HOUR(CurrentTime)
VAR MinutePart = MINUTE(CurrentTime)
VAR RoundedMinute = MROUND(MinutePart, 30)
RETURN
    IF(RoundedMinute = 60,
        TIME(HourPart + 1, 0, 0),
        TIME(HourPart, RoundedMinute, 0)
    )

This formula may look complex, but it's just a few simple steps:

• VAR CurrentTime = ...: Captures the full timestamp for the current row.
• HourPart and MinutePart: Extract respective hour and minute.
• VAR RoundedMinute = MROUND(MinutePart, 30): Rounds minutes to the nearest 30-minute mark.
• The IF statement adjusts times like 9:55 to 10:00 instead of invalid times.

After creating this column, click on it, go to Column tools, set the Data type to "Time" and the Format to your preferred time format.

You can easily adapt this for 15-minute intervals by changing the rounding multiple from 30 to 15 in the MROUND function.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Using Your New Time Groups In Visuals

Now for the fun part! Once you have your new "Time of Day" or "Time Slot" column, you can use it in any Power BI visual.

Drag your new time grouping column onto the X-axis of a bar chart. Then, drag a metric like Sales Amount or Website Sessions onto the Y-axis. Instantly, you'll have a clear visual showing performance by time period, something that was impossible to see with your raw timestamp data.

Final Thoughts

Grouping time intervals in Power BI is a fundamental step in turning granular, noisy data into clear, actionable reporting. Whether you use the simple, menu-driven interface of Conditional Columns or the flexible power of a DAX formula, you can quickly categorize your time-based data to uncover daily trends and patterns.

If you're looking for an even faster way to get answers from business reporting data, without the time spent manually grouping time intervals or wrangling with DAX, that's exactly why we built Graphed. Our platform connects directly to your data sources like Google Analytics, Shopify, and ad platforms, allowing you to ask questions in plain English, such as "show me last week's sales broken down by time of day as a bar chart." The AI handles the tricky parts of the analysis and builds interactive, beautiful charts and dashboards in seconds, so go and give Graphed a try. You'll spend less time configuring reports and more time acting on the insights. Give it a shot, we're sure you'll love it!