Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ervarble
Regular Visitor

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

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 

DataTypeIdTimeStampLocalNumericValue
10312022-06-15 09:00:00.00054743.302100
10312022-06-15 08:45:00.00054074.112000
10312022-06-15 08:30:00.00053526.713500
10312022-06-15 08:15:00.00052811.822900
10312022-06-15 08:00:00.00052237.531300

 

T_Curtailments.xlsx 

CDBTime DownTime Up
10316/10/2022 15:306/10/2022 18:40
10316/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!

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @ervarble ,

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

vkalyjmsft_0-1655866735864.png

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

vkalyjmsft_1-1655866920164.png

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.

vkalyjmsft_2-1655867165185.png

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.

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @ervarble ,

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

vkalyjmsft_0-1655866735864.png

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

vkalyjmsft_1-1655866920164.png

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.

vkalyjmsft_2-1655867165185.png

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
Super User
Super User

"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)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.