DAX - SCD logic

Hi Friends,

I had one scenario in SSAS Tabular Model, where i have 2 objects.

1) SourceData Object - which is a SCD 2 Object where we track all the history for the changes
2) Dim Date Object

I need help in writing a DAX expression where the user selects the date from Dim Date object based on that we need to populate the latest records from the SourceData Object (ie.,. the date should lie between start date and end date)

Sample SQL Code for your reference.

Create table #sourcedata
(
Source_sk int identity(1,1),
id int not null,
name varchar(500) null,
city varchar(500),
startdate date,
enddate date
)

insert into #sourcedata values(1,'santosh','US','2021-01-20','2021-05-28')
insert into #sourcedata values(1,'santosh','Canada','2021-05-29','2021-07-20')
insert into #sourcedata values(1,'santosh','London','2021-07-21','9999-01-01')
insert into #sourcedata values(2,'harish','US','2021-02-26','2021-07-12')
insert into #sourcedata values(2,'harish','france','2021-07-13','9999-01-01')
insert into #sourcedata values(3,'george','newzealand','2021-06-13','9999-01-01')
insert into #sourcedata values(4,'flintoff','australia','2021-09-13','9999-01-01')

GO
create table #DimDate
(
date_sk int not null,
Date date not null
)

insert into #DimDate values (1,'2021-06-10')
insert into #DimDate values (2,'2021-07-15')
insert into #DimDate values (3,'2021-09-18')

Desired Results in the Cube:
Case 1 : if user selects date as 2021-06-10 then below records need to be populated in cube.
id name city startdate enddate
1 santosh Canada 2021-05-29 2021-07-20
2 harish US 2021-02-26 2021-07-12

Case 2 : if user selects date as 2021-07-15 then below records need to be populated in cube.
id name city startdate enddate
1 santosh Canada 2021-05-29 2021-07-20
2 harish france 2021-07-13 9999-01-01
3 george newzealand 2021-06-13 9999-01-01

Case 3 : if user selects date as 2021-09-18 then below records need to be populated in cube.
id name city startdate enddate
1 santosh London 2021-07-21 9999-01-01
2 harish france 2021-07-13 9999-01-01
3 george newzealand 2021-06-13 9999-01-01
4 flintoff australia 2021-09-13 9999-01-01

Thanks in advance
Santosh S

amitchandak VahidDM