Fill table based on information according to date.

[Anonymous]

Hello, 

I'm currently having dificultes in fiding a way to do this. 

Basically I have two tables, one with the order information: 

Product	Date	Material
O1	01/01/2022	Iron
O2	05/02/2022	Plastic
O3	09/02/2023	Iron

And one with the supplier information: 

Supplier	ContractDateLimit	Supplies
StarkIndustries	01/01/2023	Iron
Plasticman	01/01/9999	Plastic
WaineEnterprises	01/01/9999	Iron

As you can see, the supplier of iron changed. 

What I would like to do is to create a column in the order table with this information. 

Basically this: 

Product	Date	Material	Supplier
O1	01/01/2022	Iron	StarkIndustries
O2	05/02/2022	Plastic	PlasticMan
O3	09/02/2023	Iron	WaineEnterprises

Thank you very much for the help. 

@amitchandak @Greg_Deckler @FreemanZ @Sahir_Maharaj 

[Sahir_Maharaj · Accepted solution]

Hello @Anonymous,

Supplier = 
VAR CurrentDate = Orders[Date]
VAR Material = Orders[Material]
RETURN
CALCULATE(
    FIRSTNONBLANK(Suppliers[Supplier], 1),
    Suppliers[Supplies] = Material,
    Suppliers[ContractDateLimit] >= CurrentDate
)

This formula uses a combination of the CALCULATE, FIRSTNONBLANK, and VAR functions to create a new column called "Supplier" in the Orders table. It looks up the supplier that supplies the material for each order and that has an active contract as of the order date.

Let me know if you might need further assistance.

[rajulshah]

@Anonymous ,

Can you explain by what logic are you relating Product with Supplier?

[Anonymous]

I'm sorry but I don't understand your question... Do you want to know what is the relationship between these two tables? 

The problem starts there and goes all the way to what I've said. I've tried to relate the material with supplies, but I don't think that makes a lot of sense. 

I didn't give that information because, I have no clue on what relationship should there be in this type of situations.