If Date is between two date columns in another table.

[stalerik]

Hello,

I have Table 1 with a timestamp column with one minute for each row.

I have Table 2 with a Start Date and an End Date column.  I am trying to write a simple IF or SWITCH statement for a calculated column in Table 1 where it would be TRUE if the minute row is between each Start Date and End Date pair, and FALSE if it is not.  Some Start and End Dates overlap.  I have been trying to create a calculated column in DAX but I am open to doing this in Power Query instead.

Start Date	End Date
04/26/2021 13:45	04/26/2021 14:13
04/26/2021 14:40	04/26/2021 17:03
04/26/2021 15:44	04/26/2021 23:30
04/26/2021 21:45	04/27/2021 05:28

Thanks in advance for any help/suggestions!

[Jihwan_Kim · Accepted solution]

Hi, @stalerik 

Please check the below picture and the sample pbix file for creating a new column.

Result CC =

IF (

COUNTROWS (

FILTER (

Table2,

Table1[Timestamp] >= Table2[Start Date]

&& Table1[Timestamp] <= Table2[End Date]

)

) > 0,

"True",

"False"

)

https://www.dropbox.com/s/5zsi1hg9r0gokab/stalerik.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@stalerik , Try a new column like this in Table 1

if( countx(filter(Table2,Table1[Date] >= Table2[Start Date] && Table1[Date] <=Table2[end Date]), Table2[Strat date])+0>0, true(), false())