cancel
Showing results for
Did you mean:
Helper II

## How to subtract hours from DateTime data using DAX?

I am creating a new column using DAX where I need to display a date by subtracting five hours from it.

So, if my date is 2016-12-21 09:40 pm, it should be 2016-12-21 04:40 pm.

The function duration() suggested in this post does not even show up in power bi while typing. Would appreciate yuor help.

1 ACCEPTED SOLUTION
Resolver II

Try this in DAX:

`MaxDateFiveHourDiff = MAX(Table1[DateTime]) - 5/24`

12 REPLIES 12
Helper II

Create new column with following Dax

Column = 'DateTime'[YourColumn] - TIME(0, 5, 0)
Hope it'll help.
Frequent Visitor

this works like a magic. thanks. however, the TIME format is TIME(HH,MM,SS)

Solution Sage

Power Pivot (and Excel) store stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time.  Value to the left of the decimal point are days.  The value to the right of the decimal point are a decimal representation of the 24 hour clock.

So in your case you can simple subtract 5 / 24 = .2083 from the date-timestamp to subtract 5 hours.

`new column = table[Date-timestamp] - .2083`

If you wanted to subtract 5 hours and 15 minutes from the date-timestamp you would subtract 5.25 / 24 = .2187.  If you needed to subtract 4 hours and 8 minutes, you would subtract 8 / 60 = .133 and therefore 4.133 / 24 = .17222.  And so on...

Not sure if this is the best way, but what comes to mind for me.

Helper II

Thanks for ths info.

Super User

On your table, click (...) and selet add column, let's say new column name is myHours and DAX will be something like this:

`myHours = DateDiff(myStartDate, myEndDate, hour)`

Post you referred to is related to Power Query "M" language not DAX.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

The DATEDIFF funtion will return an hour.. However, I want to just subtract five hours from my datetime value and display the subtracted date time value.

So, original datetime value: 2016-12-21 09:40 PM.  -> (Appy some dax function to subtract the hour by 5) -> New dateimt value: 2016-12-21 04:40 PM.

Resolver II

@kaka: What @parry2k meant by saying it's M rather than DAX is that the function mentioned in the linked article was applied in the Query Editor - see below.

Frequent Visitor

could you tell name of the function used here?

Helper II

Ok, so that would be an unnecessary column for me because what am actually doing in DAX is grabbing the maximum date from the table and then subtracting it's hour by 5. So, am creating a column called MaxDateFiveHourDiff:

MaxDateFiveHourDiff = MAX(due_date) - 5 hours. Question is, how do you subtract the 5 in DAX ?

Resolver II

Try this in DAX:

`MaxDateFiveHourDiff = MAX(Table1[DateTime]) - 5/24`

Frequent Visitor

I actually came here looking for someone to explain this. how does the -5/24 or +8/24 works? is it supposed to be ashortcut for datediff? can it work for days/weeks/months/years...etc?

Thanx

Helper II

Fantastic. Thank you.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors