Forum Discussion
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.
Try this in DAX:
MaxDateFiveHourDiff = MAX(Table1[DateTime]) - 5/24
12 Replies
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.
Thanks for ths info.
Create new column with following Dax
Column = 'DateTime'[YourColumn] - TIME(0, 5, 0)Hope it'll help.this works like a magic. thanks. however, the TIME format is TIME(HH,MM,SS)
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.
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.
