Forum Discussion
Fill table based on information according to date.
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
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.
3 Replies
Anonymous ,
Can you explain by what logic are you relating Product with Supplier?
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.
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.
