Measure to check whether a date/time in one table is between 2 date/time fields in a second table.

[ervarble]

I have one table with the system-wide power demand in 15-minute increments.  This table is coming from a direct query, so I can't add columns to it.  I have a second table that has information on power curtailments to avoid high demand, including a start and end time.  Both tables have records from other datasets, so I also need to filter on DataRecords.DataTypeID = T_Curtailments.CDB = 1031. I want to create a measure that will indicate whether we were curtailed during a period - e.g. for each period in DataRecords, does the time stamp fall between the down and up times in T_Curtailments.

DataRecords.csv 

DataTypeId	TimeStampLocal	NumericValue
1031	2022-06-15 09:00:00.000	54743.302100
1031	2022-06-15 08:45:00.000	54074.112000
1031	2022-06-15 08:30:00.000	53526.713500
1031	2022-06-15 08:15:00.000	52811.822900
1031	2022-06-15 08:00:00.000	52237.531300

T_Curtailments.xlsx 

CDB	Time Down	Time Up
1031	6/10/2022 15:30	6/10/2022 18:40
1031	6/12/2022 16:00	6/12/2022 18:30

From reading the forum, I have a good idea of how I could do this if I could add a column to the DataRecords table, but since I can't and I need it to be a measure instead I'm getting stuck.

Thanks for your help!

[v-yanjiang-msft · Accepted solution]

Hi @ervarble ,

In your provided DataRecords table, the TimeStampLocal only contains minutes and seconds.

So I create a sample contain a row falls in the power curtailments period.

If you prefer a measure, you can modify @lbendlin 's solution slightly:

Measure =
VAR a =
    FILTER (
        'T_Curtailments',
        'T_Curtailments'[Time Down] <= MAX ( 'DataRecords'[TimeStampLocal] )
            && 'T_Curtailments'[Time Up] >= MAX ( 'DataRecords'[TimeStampLocal] )
    )
RETURN
    IF ( COUNTROWS ( a ) > 0, 1, 0 )

It can get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

"This table is coming from a direct query, so I can't add columns to it."

Who says that?

Curtailed = 
var a = FILTER(Curtailments,Curtailments[Time Down]<='in'[TimeStampLocal] && Curtailments[Time Up]>='in'[TimeStampLocal])
return if(COUNTROWS(a)>0,1,0)