Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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.
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())
Hi All,
I have attached the column [Notification Date] below and I tried the simple solution using measure:
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.
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 @gree1502,
What do you want the new column to show the difference in? Hours, minutes or seconds? Or perhaps a text string?
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
112 | |
99 | |
69 | |
67 |