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

[gree1502]

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 

[malagari]

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

[gree1502]

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

[parry2k]

You need to add this as a column not measure

[gree1502]

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

[parry2k · 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)
)

[gree1502]

Thank you. It worked:)

[gree1502]

Hi,

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

I am doing something wrong?

Thanks

[parry2k]

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.

[Phil_Seamark]

Hi @gree1502,

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

[gree1502]

Hi Phil_Seamark,

The difference should be in hours.

Thanks

[parry2k]

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)