Power Query M formula - Calculate column based on criteria from two tables

Question

I have two query tables: Sales and Prices. Below is just example data.

Sales:

Start date | End date | Quantity 1 | Quantity 2
1.3.2022 | 2.3.2022 | 10 | 15
1.3.2022 | 3.3.2022 | 15 | 20

Prices:

Date | Price 1 | Price 2
1.3.2022 | 2 | 4
2.3.2022 | 3 | 2
3.3.2022 | 1 | 3

Now, I want to add calculated column to Sales table. For each row I want to do sumproduct of Quantity 1 and Quantity 2 with Price 1 and Price 2 for each day in period from start to end date, and sum it.

For example, for the first row of Sales table it should be calculated as:

(10 x 2 + 15 x 4) + (10 x 3 + 15 x 2)

Also, it can be calculated as sum of price columns for specific date range and then multiplied with quantities:

10 x (2 + 3) + 15 x (4 + 2)

Any suggestions?

Answer 1

Power query is not currently supported here on Q&A. The product group for Power Query actively monitors questions over at
https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

--please don't forget to Accept as answer if the reply is helpful--