Use specific exchange rate date in revenue calculation

[jonasboye]

Hi all,

I have a challenge that I hope I could get some help/support to point me in the right direction.

I have created a revenue report where the total revenue for the company is calculated in the company currency.I have a table containing exchange rate in a format similar to the one below (it contains multiple currencies).

FromCurrency	ToCurrency	StartDate	EndDate	Rate
EUR	DKK	01-01-2019	31-01-2019	746,37
EUR	DKK	01-02-2019	28-02-2019	745,85
EUR	DKK	01-03-2019	31-03-2019	745,23
EUR	DKK	01-04-2019	30-04-2019	746,07

And I have a separate table containing invoiced sales order lines. This includes detail of the currency the order was invoiced in and the invoice date.

I already have formula to identify the correct exchange rate for each invoice line based on the invoice date and this is working correctly.

ExchangeRate = IF(SalesInvoiceLines[CurrencyCode]="DKK";100;CALCULATE(FIRSTNONBLANK(ExchangeRates[Rate];ExchangeRates[Rate]);FILTER(ExchangeRates;ExchangeRates[StartDate].[Date]<=SalesInvoiceLines[InvoiceDate].[Date]&&ExchangeRates[EndDate].[Date]>=SalesInvoiceLines[InvoiceDate].[Date]&&ExchangeRates[FromCurrency]=SalesInvoiceLines[CurrencyCode]&&ExchangeRates[ToCurrency]="DKK")))

My challenge is that I have a request of being able to calculate the revenue per line based on a fixed exchange rate date rather than the invoice date, so that the same exchange rate is used on all invoice lines.

It should be possible to specify the specific exchange rate date directly in the report and it should then dynamically calculate the total revenue based on that specific exchange rate date.

Any help would be greatly appreciated.

Thanks

Jonas

[v-xuding-msft]

Hi @jonasboye ,

For your scenario, I can't create a sample to implement it. Can you please post more sample data and the expected result? 

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonasboye]

Hi Xue,

I have created a sample file. You should be able to download it using this link.

The table SalesInvoiceLine has a calculated column called ExchangeRate where I am fetching the exchange rate to DKK, but the formula is getting the exhange rate based on the invoice date. 

I would like to be able to specify the specific date that the exchange rate should be based on. 

Regards

Jonas

[v-xuding-msft]

Hi @jonasboye ,

Can you please attach a new link? When I open it, I get the message below.

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonasboye]

Hi Xue,

Sorry about that. Can you try the link below. 

https://daxiomatic-my.sharepoint.com/:u:/g/personal/jab_daxiomatic_dk/ER-T6GBHaEhFsGqzDO1cTw8BZFT-Sn... 

/Jonas

[Greg_Deckler]

Investigate the use of "What If" parameters.