Calculated column to determine if date is between 2 dates on another table

[Anonymous]

Hi, hopefully someone can help me with this. I have a table that looks like the following

ID              CreatedDate       Actions

3334          01-Jun-2019          2

3334          29-Jun-2019          1

There is also another table linked using ID that looks like the below

ID                     Start                   End

3334          01-May-2019           15-Jun-2019 

What I want is to add a calculated column to the first table that says if the CreatedDate is between Start and End on the second table, then 1, else 0.

So an ideal output for ID 3334 would be 

ID              CreatedDate       Actions          Flag

3334          01-Jun-2019          2                  1

3334          29-Jun-2019          1                  0

Can someone please help with this? 

Appreciate any help

Thank you

[TomMartens · Accepted solution]

Hey,

wondering if there is just one row per id in the 2nd table?

If this is the case then this DAX can be used to create a calculated column:

a new calculated column = 
var _ID = '<1st table>'[ID]
var _CreatedDate = '<1st table>'[CreatedDate]
var startdate = LOOKUPVALUE('<2nd table>'[Start] , '<2nd table>'[ID] , _ID)
var enddate = LOOKUPVALUE('<2nd table>'[End] , '<2nd table>'[ID] , _ID)
return
IF(AND ( _CreatedDate >= startdate , _CreatedDate <= enddate) , 1 , 0)

Hopefully this is what you are looking for

Regards,

Tom

[TomMartens · Accepted solution]

Hey,

wondering if there is just one row per id in the 2nd table?

If this is the case then this DAX can be used to create a calculated column:

a new calculated column = 
var _ID = '<1st table>'[ID]
var _CreatedDate = '<1st table>'[CreatedDate]
var startdate = LOOKUPVALUE('<2nd table>'[Start] , '<2nd table>'[ID] , _ID)
var enddate = LOOKUPVALUE('<2nd table>'[End] , '<2nd table>'[ID] , _ID)
return
IF(AND ( _CreatedDate >= startdate , _CreatedDate <= enddate) , 1 , 0)

Hopefully this is what you are looking for

Regards,

Tom

[Cmcmahan]

Sure.  Be sure to update this with <= where appropriate.

Flag = IF( Table2[Start] < [CreatedDate] && [CreatedDate] < Table2[End], 1, 0)

I haven't tried this in actual PowerBI, so if you run into an issue where it's not recognizing Table2[Start]/Table2[End],  use RELATED(Table2[Start]) to fix it.

[Anonymous]

Does this take into account that each ID has different Start and End?

[Cmcmahan · Accepted solution]

It should automatically use relationships between tables.  If it doesn't, make sure you have a relationship between them set up on linked ID#s.  If it STILL isn't working, or starts to complain about how a single value can't be determined, try using RELATED to get the specific value.