cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kaka Helper II
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

Accepted Solutions
Highlighted
danrmcallister Resolver II
Resolver II

Re: How to subtract hours from DateTime data?

Try this in DAX:

 

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

 

View solution in original post

11 REPLIES 11
Super User IV
Super User IV

Re: How to subtract hours from DateTime data?

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. 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





kaka Helper II
Helper II

Re: How to subtract hours from DateTime data?

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.

danrmcallister Resolver II
Resolver II

Re: How to subtract hours from DateTime data?

@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.

 

PBI Modify Time.jpg

kaka Helper II
Helper II

Re: How to subtract hours from DateTime data?

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 ? 

Highlighted
danrmcallister Resolver II
Resolver II

Re: How to subtract hours from DateTime data?

Try this in DAX:

 

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

 

View solution in original post

mattbrice Solution Sage
Solution Sage

Re: How to subtract hours from DateTime data using DAX?

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.

kaka Helper II
Helper II

Re: How to subtract hours from DateTime data?

Fantastic. Thank you. 

kaka Helper II
Helper II

Re: How to subtract hours from DateTime data using DAX?

Thanks for ths info. 

Arun1
Frequent Visitor

Re: How to subtract hours from DateTime data?

could you tell name of the function used here?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors