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.