Merging Two tables, one that has a continuous date column, and one that has a discrete date column

[aar0n]

Hey guys,

I need to merge two tables in dax.. my "Online Date Table" Looks something like below..
Online Date:

Name	Online Date	Location
aaa	1/1/2010	x
aaa	5/1/2012	y
bbb	4/1/2015	x
ccc	1/1/2011	x
ccc	3/1/2011
ccc	6/1/2011	y

My Production Table looks like something below

Production Table:

Name	Value	Date
aaa	10	1/1/2010
aaa	2	2/1/2010
aaa	4	3/1/2010
…	…	…
ccc	5	1/1/2011
ccc	2	2/1/2011
ccc	3	3/1/2011
ccc	7	4/1/2011
ccc	5	5/1/2011
ccc	4	6/1/2011
ccc	2	7/1/2011

What i need to do is to merge both, so that i can see the "Online Date" and "Location" inside of my Production Table. i need to have that value show up for every "Name" and "Date" up until a new "Online Date" exceeds the True "Date" column.

for example, 

Example of Merged Table:

Name	Value	Date	Online Date	Location
aaa	10	1/1/2010	1/1/2010	x
aaa	2	2/1/2010	1/1/2010	x
aaa	4	3/1/2010	1/1/2010	x
…	…	…	…	…
ccc	5	1/1/2011	1/1/2011	x
ccc	2	2/1/2011	1/1/2011	x
ccc	3	3/1/2011	3/1/2011
ccc	7	4/1/2011	3/1/2011
ccc	5	5/1/2011	3/1/2011
ccc	4	6/1/2011	6/1/2011	y
ccc	2	7/1/2011	6/1/2011	y

[v-yuezhe-msft · Accepted solution]

@aar0n,

Create new table using DAX  below.

Merge = 
GENERATEALL (
    Production,
    VAR Tdate =Production[Date]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( 'Online Date', 'Online Date'[online date] = Tdate ),
            "online date",'Online Date'[online date],
            "Location", 'Online Date'[location]
            
        )
)

Create the following columns in the new table below.

New onlinedate = 
IF (
    Merge[online date]= BLANK (),
    CALCULATE (
        LASTNONBLANK ( Merge[online date], Merge[online date] ),
        FILTER ( ALLEXCEPT ( Merge, Merge[Name] ), Merge[Date] <= EARLIER ( Merge[Date]) )
    ),
    Merge[online date]
)

New Location = 
IF (
    Merge[Location] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Merge[Location], Merge[Location] ),
        FILTER ( ALLEXCEPT ( Merge, Merge[Name] ), Merge[Date] <= EARLIER ( Merge[Date])&&Merge[New onlinedate]=EARLIER(Merge[New onlinedate]) )
    ),
    Merge[Location]
)

Regards,
Lydia

[aar0n]

sorry for the late reply, but thank you very much! this is exactly what i was trying to do!