Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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.
Hi Xue,
Sorry about that. Can you try the link below.
/Jonas
Investigate the use of "What If" parameters.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |