How to add and subtract hours and minutes to a datetime in Power BI?

Here’s a question from Kevin:

I have a date time column in one of my Power BI data model table representing a timestamp. I would like to create a few additional columns to the table each of those will increment the first column by 08:30 hours, which is our default shift duration. Any ideas on how to do this as i do not find a suitable DAX formula?

Add time to a datetime column in Power BI

Creating a column of timestamps

If you don’t have a date time filed defined in your table, and want to follow along with this tutorial you can start by creating a very simple date column using the CALENDAR function in DAX:

Dates = CALENDAR (DATE (2024,1,20), DATE(2024,2,5))

Important Note: If you are loading your own data from an external file of database table, make sure that the date type of your column is of type date or date / time.

Adding time to a timestamp in Power BI

To offset a time value in Power BI, use the DAX function TIME. The syntax is simple:

New_Time_Column  = Existing_Time_Column  + TIME(offset in hours, offset in minutes, offset in seconds)
  • In our case you would like to add 08:30 hours. Start by navigating to the Data tab.
  • Then hit the New Column button.
  • Next type the following DAX:
First Shift Start = Dates[Date]+TIME(8,30,0)

This will create a new calculated column as shown below:

Troubleshooting: avoid mismatched data types or invalid time value error messages by ensuring that the data type column you will be adding or subtracting values from is a date / time.

Subtract hours, minutes and seconds in Power BI

In the same fashion we can also subtract time:

  • In your Power BI report, navigate to the Data View.
  • Then hit New Column.
  • Type the following DAX and hit Enter.
Prep Start = Dates[Date] - TIME(2,30,0)
  • This expression creates a new calculated column containing a timestamp that deducts 2:30 from our Date Time column.
  • Here’s our table:
  • Save your report.