Nick Momin Nick Momin - 29 days ago 19
SQL Question

Excel / DAX / Power Query: How to filter table to find records that fill within a 24 hour window from two dates

got my head stumped with this one. Not even sure if its possible.

I have 2 dates column: "Date_of_Call" and "Error_Open_Time"

I want to filter this report to only show records where Error_Open_Time falls within a 24 hour window from Date_of_Call time.

Example:

If Error_Open_Time = '11/6/2016 1:17:00 PM' and Date_of_Call = '11/6/2016 12:00:00 AM', then this record SHOULD show up in the filter since Error_Open_Time falls within the 24 hour window.

If Error_Open_Time = '12/11/2016 12:35:00 AM' and Date_of_Call = '12/10/2016 12:00:00 AM', then this record SHOULD NOT show up in the filter since Error_Open_Time DOES NOT fall within the 24 hour window.

Also, if Error_Open_Time date is before Date_of_Call date then it should be filtered out since it is not necessary.

I hope this makes sense. Thanks for all your help!

Answer

You can subtract dates together in Power Query, which will give you a duration. If the Error_Open_Time and Date_of_Call columns are Date or DateTime types, then this should do the trick:

  1. Add a custom column with the formula [Error_Open_Time] - [Date_of_Call]
  2. Filter the custom column to be between a 24-hour period. You can do "is greater than or equal to" -1.00:00:00 and "is less than or equal to" 1.00:00:00 (between 1 day).
  3. Remove the custom column

The steps should look like this:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Error_Open_Time] - [Date_of_Call]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] >= #duration(-1, 0, 0, 0) and [Custom] <= #duration(1, 0, 0, 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})