Merge tables based on time function?

[herbemischung]

Short introduction:

I've got a table with all bought products and their price:

Article ID | Date (received on) | Costs | ID_Entry

--> everytime we buy an article it gets an ID_Entry. 

00001 | 1.1.2019 | 0,50 | ENTR_01

00002 | 1.1.2019 | 2,48 | ENTR_02

.......

00001 | 5.3.2019 | 0,60 | ENTR_2315

We also have the possibility to correct the article status. This also generates an ID_Entry (in a different table) but without costs. 

Table B

00001 | 6.6.2019 | ENTR_3571 

Each sale refers to the ID_Entry. 

If we sell article 00001 (Fifo) with the Entry_ID ENTR_01 the costs are 0,5 / with ENTR_2315 0,6 

but there are no costs with Entr_3571.

I would need to get the costs of the latest article access below the entry date of the correction.

last access for article 00001 below 6.6.2019 -->5.3.2019 -->0,60

not sure if this can be done. thanks for help!

[v-piga-msft]

Hi @herbemischung ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample with table format and your desired output so that we could understand your logic better.

Best  Regards,

Cherry

[herbemischung · Accepted solution]

Yes, @Mariusz helped me with a formula. I am sorry that I am not (yet) able to fully understand and therefore explain it. But here is the code:

"let _art = [idArticle], _date = [Entry_date]
in
Table.FirstN(
Table.SelectRows(
Table.Sort( #"Table A", {{"Entry_date", Order.Descending}}), each [idArticle] = _art and [Entry_date] <= _date), 1
)"

[Mariusz]

Hi @herbemischung 

Can you create a data sample for both tables and one for the desired outcome.

Best Regards,
Mariusz

Please feel free to connect with me.