Forum Discussion
Time difference between now and DateTime column
Hello precious community!
I searched and found many threads on how to calculate the time difference between two DateTime columns, but couldn't find one on how to calculate the time difference between the time the dataset is refreshed and a DateTime column.
The objective is to be able to identify if the creation time of a ticket is greater than 24 hours. Maybe I'm approching this the wrong way.
Thanks in advance!
GMichaud , I would suggest hour. That will give better control. divide by 24 to can days in decimal
5 Replies
Hi GMichaud ,
One way to get the refresh time is to create a new blank query in Power Query and, in the formula bar, type
= DateTime.FixedLocalNow()When you apply this to the model, you should just end up with a datetime value which you can reference in DAX calculations like DATEDIFF etc.
Pete
GMichaud , You can create a new column as
New column = if(datediff([created_at], now(), hour)>1,1 , 0)
It seems to do the intended work. Could you confirm my comprehension of DateDiff please? If let's say a ticket is opened at 23:59:59 on a given date, does DateDiff give 1 between that day and the next day at 00:00:00 if I choose to compare by DAY? If so, will it give a better answer if I ask it to compare by HOUR and choose greater than 24? Thanks again!
I forgot to tag you in my reply, here it is again:
It seems to do the intended work. Could you confirm my comprehension of DateDiff please? If let's say a ticket is opened at 23:59:59 on a given date, does DateDiff give 1 between that day and the next day at 00:00:00 if I choose to compare by DAY? If so, will it give a better answer if I ask it to compare by HOUR and choose greater than 24? Thanks again!
GMichaud , I would suggest hour. That will give better control. divide by 24 to can days in decimal
