Apply currency exchange from another table

[Anonymous]

I have the following tables:

- invoices with curency ID

- currencies with curency Id and exchange ID

- exchange rates with exchange Id and exchange rate I want for a day

I want to apply this to calculation as follows: so I can multiply the SUM by the average of exchange rates. I tried to add *[average exchange rates ] but doesnt work. How can I do this?

PurchasingPrice_Aug22 = CALCULATE(SUM(invoices_granular[items.purchasingPrice]),(DATESBETWEEN(invoices_total[createdAt].[Date], DATE(2022 ,08 ,01 ), DATE(2022 ,08 ,31 ))))

[123abc]

To calculate the PurchasingPrice_Aug22 by multiplying the SUM of purchasing prices by the average exchange rate for August 2022, you can use the following DAX formula. Assuming that you have a relationship between the invoices table and the currencies table through the currency ID, and between the currencies table and the exchange rates table through the exchange ID, you can create a measure as follows:

PurchasingPrice_Aug22 =
VAR StartDate = DATE(2022, 8, 1)
VAR EndDate = DATE(2022, 8, 31)
VAR CurrencyFilter = FILTER(currencies, [currency ID] = invoices_granular[items.currency ID])
VAR ExchangeRatesForAugust =
FILTER(exchange rates,
[exchange Id] = CurrencyFilter[exchange ID] &&
exchange rates[Date] >= StartDate &&
exchange rates[Date] <= EndDate
)
VAR AverageExchangeRate = AVERAGEX(ExchangeRatesForAugust, exchange rates[exchange rate])
RETURN
SUM(invoices_granular[items.purchasingPrice]) * AverageExchangeRate

This formula does the following:

1. It defines two variables, StartDate and EndDate, to specify the date range for August 2022.

2. It uses the FILTER function to filter the currencies table to only include the rows where the currency ID matches the currency ID in the invoices_granular table.

3. It uses the FILTER function again to filter the exchange rates table to include only the rows where the exchange ID matches the exchange ID from the filtered currencies table, and where the date falls within the specified date range for August 2022.

4. It calculates the average exchange rate for August 2022 using the AVERAGEX function.

5. Finally, it returns the SUM of invoices_granular[items.purchasingPrice] multiplied by the calculated AverageExchangeRate to get the PurchasingPrice_Aug22.

Make sure that the column and table names in the formula match the names in your actual data model.