Calculate whether a date/time is within a list of date/time ranges in another table

[bpmccain]

I have a table of data with date/times and corresponding values in it

DateTime	Value
January 1, 2024 10:00 AM	1
January 1, 2024 11:00 AM	3
January 1, 2024 12:00 AM	4
January 1, 2024 1:00 PM	2

In another table, I have a list of date/time ranges that I want to exclude

Exclusion Start Time	Exclusion End Time
January 1, 2024 10:46 AM	January 1, 2024 11:15 AM
January 1, 2024 12:46 PM	January 1, 2024 1:12 PM

I want to get a table that shows the following

DateTime	Value	Exclude
January 1, 2024 10:00 AM	1	0
January 1, 2024 11:00 AM	3	1
January 1, 2024 12:00 AM	4	0
January 1, 2024 1:00 PM	2	1

I have no problem doing this when there is only one time range per day, but don't know how to do it when there are multiple time ranges per day. I am currently using:

In Exclusion Range = CALCULATE(IF(AND(MAX('Table 1'[DateTime])>=MAX('Table 2'[Exclusion Start Time]),MAX('Table 1'[DateTime])<=MAX('Table 2'[Exclusion End Time])),1,0))

But of course using MAX just gives me whether it is in the last time range of the day. Any ideas?

[Ray_Minds]

You will go with the below measure for expected solution
Measure :
Exclude =
VAR CurrentDateTime = sumx('Main Table','Main Table'[MainDateTime])
VAR IsExcluded =
    CALCULATE(
        COUNTROWS('Range Table'),
        FILTER(
            'Range Table',
            CurrentDateTime >= 'Range Table'[Exclusion Start Time] &&
            CurrentDateTime <= 'Range Table'[Exclusion End Time]
        )
    )
RETURN
    IF(IsExcluded > 0, 1, 0)

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[Shashankgodala]

Hello @bpmccain 

This solution works for both of the following cases:

1. Multiple exclusion timestamps in a day.

2. An exclusion period includes 2 or more days. 

Let us call the tables as ValueTable and ExclusionTable.

Follow the below steps:

1. Extract the dates from Timestamp of ValueTable and Exclusion Start Time from Exclusion Table. 

2. Merge the ValueTable with ExclusionTable and expand all the rows.

= Table.NestedJoin(#"Inserted Date", {"Date"}, ExclusionTable, {"Date"}, "TimestampTable", JoinKind.LeftOuter)

= Table.ExpandTableColumn(#"Merged Queries", "TimestampTable", {"Exclusion Start Time", "Exclusion End Time"}, {"TimestampTable.Exclusion Start Time", "TimestampTable.Exclusion End Time"})

3. Add a custom column on the condition that the Timestamp is within the exclusion zone. (Return 0 if a timestamp is within the zone)

= Table.AddColumn(#"Expanded TimestampTable", "Valid Value", each if [TIMESTAMP] > [TimestampTable.Exclusion Start Time] and [TIMESTAMP] < [TimestampTable.Exclusion End Time] then 0 else [VALUE])

4. Group the Timestamp from the ValueTable by aggregating both original value and conditional column value from the previous step. 

= Table.Group(#"Added Custom", {"TIMESTAMP"}, {{"Original Value", each List.Sum([VALUE]), type nullable number}, {"Valid Value", each List.Sum([Valid Value]), type number}})

5. And finally, a conditional column to find out which timestamps have fallen into atleast one exclusion zone.

= Table.AddColumn(#"Grouped Rows", "Value Exclude", each if [Original Value] = [Valid Value] then [Original Value] else 0)

There could be a quicker solution but hope this works for you 🙂

[bpmccain]

This works as well, but is a lot slower than the other solution considering the data tables are refreshed daily. Thanks for your help.

[Jihwan_Kim · Accepted solution]

Hi,

I assume the expected result is calculated column?

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Expected result calculated column =
VAR _datetime = 'Table 1'[DateTime]
VAR _condition =
    COUNTROWS (
        FILTER (
            'Table 2',
            'Table 2'[Exclusion Start Time] <= _datetime
                && 'Table 2'[Exclusion End Time] >= _datetime
        )
    ) > 0
RETURN
    IF ( _condition, 1, 0 )

[bpmccain]

Works great. Thanks