How to add a custom column to check if a date is within 12 hours from now?

[whatisdata96]

Hi all - the title says it all. 

I have call center data that shows when agents will submit tickets. I have one date/time column that is called "Created Time". I want to add a conditional/custom column that would check if this date in that column is dynamically within 12 hours of right NOW dynamically.

Should I not do this in Power Query? Should I open up Power Pivot and do this in DAX? My end hope is to make it possible to throw all the data into a Pivot and filter if the data is within that 12-hour window of the date/time now.

Appreciate any help here.

[AlexisOlson · Accepted solution]

Since calculated columns (whether DAX or Power Query) only update upon refreshing the data model, you may want to consider writing a measure that you can use as a filter on a visual.

For example, if your measure looked something like this (untested):

IsInNext12Hours =
VAR _DateToCompare = MAX ( Table1[Date] )
VAR _HourDiff = DATEDIFF ( NOW (), _DateToCompare, HOUR )
RETURN
    IF ( _HourDiff < 12, 1, 0 )

Then you could set [IsInNext12Hours] is 1 in the filter pane of a visual.

[edhans]

You want to use the DateTime.IsInNextNHours() function. As of the time of this posting, 4pm on August 4 is within 12hrs, 4pm of August 5th is not.

That will only happen on the refresh though. Once it is in Power BI it will go "stale" unless you are frequently refreshing (more frequently than every 12 hrs. A Calculated Column in DAX would have the same issue - it only updates on refresh.