How to return records only if the record falls between Start and End Dates

[sd_kevin]

Hi,

Is it possible to only return records from a table where the records has a StartDate and an EndDate that is between the user selected date from another unrelated table?  For example when the user selects a value (date) from a slicer linked to another table and the SELECTEDVALUE is between the StartDate and EndDate.  

Slicer will be based on this table

This will be where the records will be returned from

At this point I am not even sure how to join/create a relationship between these two tables.  I don't neccessary need to have any relationship between the two tables, but just need some way to use what was selected from the EffDateTable to filter what is returned on the OnLeaveTable (and also another table).

TIA!

Kevin

[sd_kevin]

@Greg_Deckler @mahoneypat @sevenhills @amitchandak 

Thank you all for your solutions and quick responses!!!  I love this community!  I learned so much just reviewing all the solutions you provided.  All the solutions worked.  Since @Greg_Deckler responded first I will mark his as the solution.

Thank you all again.  Your help was very much appreciated!

Warm regards,

Kevin

[amitchandak]

@sd_kevin , Try a measure like

Measure =
var _max = maxx(allselected(EffDateTable),EffDateTable[EffectiveDate])
return
Calculate(Count(Epluyees), OnLeaveTable[LeaveDate]<= _max, OnLeaveTable[ReturnDate]>=_max)

[mahoneypat]

Yes.  With no relationship between the tables, make a slicer with the EffectiveDate and a table visual with your onleave column (and leave and return date if desired), and this measure:

SelectedDateInRange = var selecteddate = selectedvalue(EffDateTable[EffectiveDate])

return Calculate(Countrows(OnLeaveTable), OnLeaveTable[LeaveDate]<= selecteddate, OnLeaveTable[ReturnDate]>=selecteddate)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[sevenhills]

Optional: If you are taking above @mahoneypat, use today date for null Return date

var selecteddate = selectedvalue(EffDateTable[Effective Date])

return Calculate(Countrows(OnLeaveTable), OnLeaveTable[LeaveDate]<= selecteddate, IF(ISBLANK(OnLeaveTable[ReturnDate]), TODAY(), OnLeaveTable[ReturnDate]) >=selecteddate)

[sd_kevin]

@sevenhills 

Thank you so much for this option!  I definitely used it!

Warm regards,

Kevin

[Greg_Deckler · Accepted solution]

@sd_kevin  - So this is like what I call a Complex Selector. In your case it could go like this:

Measure = 
  VAR __Date = SELECTEDVALUE('EffDateTable'[Effective Date])
  VAR __LeavdDate = MAX('OnLeaveTable'[LeavdDate])
  VAR __ReturnDate = MAX('OnLeaveTable'[ReturnDate])
RETURN
  IF(__LeavdDate >= __Date && __ReturnDate <= __Date,1,0)

 You can then use your OnLeave column in a table visualization and then Measure in the Filters pane for example.

[sd_kevin]

@Greg_Deckler 

Thank you the solution and quick response!