Calculated Column: Apply Value for Time Span from other Table

[Anonymous]

Hi there,

I would like the output of table 2 "Desired Output Column"

The output is the ID from Table1 when Date2 is >= Date1 and Date2 < Date1 (index+1) . See table2 for example.

Table1

Date1	ID
1/1/2018	Cat
2/1/2018	Dog
3/2/2018	Rabbit

Table2

Date2	Value	Desired Output Column
1/1/2018	5	Cat
1/2/2018	6	Cat
1/3/2018	3	Cat
1/4/2018	6	Cat
1/5/2018	3	Cat
2/1/2018	4	Dog
2/2/2018	6	Dog
2/3/2018	3	Dog
2/4/2018	1	Dog
2/5/2018	1	Dog
3/3/2018	3	Rabbit
3/4/2018	2	Rabbit
3/5/2018	3	Rabbit
3/6/2018	4	Rabbit
3/7/2018	5	Rabbit

Any thoughts? thanks!

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this calculated column

Column =
VAR FirstDate_For_ID =
    CALCULATE ( MAX ( Table1[Date1] ), FILTER ( Table1, [Date2] >= [Date1] ) )
RETURN
    CALCULATE ( MIN ( Table1[ID] ), Table1[Date1] = FirstDate_For_ID )

[Zubair_Muhammad]

@Anonymous

Please see attached file as well