Determine a Column based on a different column based on date interval

[vojtechsima]

Hello,

I have these tables (Name = Subscription Name):

I would like to determine the Entity of each record (in the first Table, taken from the Second Table), However, the Name can have two or more Entities, which means I have to evaluate when the record belongs to which Entity based on the FROM/TILL Columns.  So Basically I want to display a New column in the First Table that will show the proper entity for the Date.

Can you help me with this?

[amitchandak · Accepted solution]

@vojtechsima , a new column in table 1

maxx(filter(Table2, Table1[Name] = Table2[Subscription Name] && Table1[Date] >= Table2[from] && (Table1[Date] <= Table2[to] || isblank(Table2[to]))), Table2[Entity])

[vojtechsima]

@amitchandak Thank you very much! This did the trick.