Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

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)
)


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.

View solution in original post

11 REPLIES 11
malagari
Responsive Resident
Responsive Resident

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

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

You need to add this as a column not measure



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.

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

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)
)


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.

Thank you. It worked:)

Hi,

 

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

 

I am doing something wrong?

 

Thanks

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

 

 



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.

Phil_Seamark
Microsoft Employee
Microsoft Employee

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!

Hi Phil_Seamark,

 

The difference should be in hours.

 

Thanks

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)

 



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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.