cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gree1502 Helper II
Helper II

How to subtract a column with Date and time from current time

Hi,

 

I am having problem to figure out how to subtract a column with Date and time from current time.

 

I have a column with unique ids. Another column with date and time when these ids are created. 

 

I want to extract those ids when its corresponding time is subtracted from current time and the difference > 1.

 

Any help is appreciated.

 

Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: How to subtract a column with Date and time from current time

Seems like you have value in notification date  in the future, how you want to handle that, show negative value if value is in future. Try following calculation, it will give you value in negative in case notification date > now()

 

TimeDiff =if( Table[Notification Date]> NOW(),
-DATEDIFF(NOW(),Table[Notification Date], HOUR)
DATEDIFF(Table[Notification Date], NOW(), HOUR)
)





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.





View solution in original post

11 REPLIES 11
Microsoft Phil_Seamark
Microsoft

Re: How to subtract a column with Date and time from current time

Hi @gree1502,

 

What do you want the new column to show the difference in?  Hours, minutes or seconds?  Or perhaps a text string?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

malagari Resolver IV
Resolver IV

Re: How to subtract a column with Date and time from current time

I'm not sure whether you're trying to show Unique IDs that have a corresponding Datetime in the past or future, but you should use DAX Time Intelligence functions instead of trying to substract the current time.

You can read up on it a bit more here: https://msdn.microsoft.com/en-us/library/ee634763.aspx

 

In your case, you should simply be able to do either in a calculation:

 

[CorrespondingDatetime] < NOW()

OR 

[CorrespondingDatetime] > NOW()

 

Let's assume you want to create a custom column that is TRUE if the [CorrespondingDatetime] is in the past.

 

isInPast = IF([CorrespondingDatetime] < NOW(), TRUE(), FALSE())
Dan Malagari
Consultant at Headspring
gree1502 Helper II
Helper II

Re: How to subtract a column with Date and time from current time

Hi Phil_Seamark,

 

The difference should be in hours.

 

Thanks

Super User IV
Super User IV

Re: How to subtract a column with Date and time from current time

You can add following column or can also do in one column:

 

 

HoursPassed = datediff(Table1[MydateTimeField], now(), hour)

 

 

 

IsThisInPast = if(Table1[HoursPassed]>1, TRUE(), FALSE())

 

or you can do in one calculation

 

Spoiler
IsThisInPastOneCalculation =if( datediff(Table1[MydateTimeField], now(), hour) > 1, TRUE, FALSE)

 






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.





gree1502 Helper II
Helper II

Re: How to subtract a column with Date and time from current time

Hi,

 

Thanks for the response. Itseems like while creating custom columns in Edit queries, NOW() is not recognised.

 

I am doing something wrong?

 

Thanks

Super User IV
Super User IV

Re: How to subtract a column with Date and time from current time

Hey @gree1502

 

It was DAX expression, not in Edit Queries. On your desktop canvas, click ... on your table and then choose "New Column", see below.

 

addcolumn.PNG

 

 






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.





gree1502 Helper II
Helper II

Re: How to subtract a column with Date and time from current time

Hi All,

 

I have attached the column [Notification Date] below and I tried the simple solution using measure:

 

Capture.PNG

 

TimeDiff = DATEDIFF(NOW(), Table[Notification Date], HOUR)

 

But it isn't taking the column. It says, the following:

A single value for column 'Notification Date' in table 'TABLE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Can anyone help me with this one?

 

Thanks

Super User IV
Super User IV

Re: How to subtract a column with Date and time from current time

You need to add this as a column not measure






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.





gree1502 Helper II
Helper II

Re: How to subtract a column with Date and time from current time

Hi,

 

Now after adding "New column instead of new measure". I get the following error:

 

In DATEDIFF function, the start date cannot be greater than the end date.

 

My query is:

 

TimeDiff = DATEDIFF(Table[Notification Date], NOW(), HOUR)

 

Thanks

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors