Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ElenaGrom
Frequent Visitor

Adjust Exchange Rates based on user-selected period

Hello, Community!

 

I have the requirement to:

 1) calculate the YTD Sales amount in Local currency based on the last reporting month, selected by the user (this one is done)

 2) Where my problem is: recalculate the result from Step 1 into Reporting currency (EUR), but with the Exchange Rate valid on the month, which the user marked as "last reporting".

E.g.,

if the user selects "March 2022", the YTD period is 01.01.2022-31.03.2022, the historical Exch Rate should be taken as of 31.03.2022.

if the user selects "February 2022", the YTD period is 01.01.2022-28.02.2022, the historical Exch Rate should be taken as of 28.02.2022.

I have:

- Fact table, which contains amounts in local currencies, transaction dates and currency codes. 

 
Fact_Table.jpg


- Exchange Rates table with currency codes and historical exchange rates

 

Exch_Rate_Table.jpg

- Date slicer, where the user is asked to specify the "last reporting month". (First reporting month is always January of the selected year). From that selection I derive via measures:

 

Sales Year to Date: 

SalesYTD = CALCULATE(sum('Fact'[Sales Local Currency]),DATESYTD('Calendar'[Date]))

Adjusted Rate: 

AdjustedRate = calculate(sum(ExchRates[Rate]),ExchRates[Currency] in values ('Fact'[Local Currency]))

EUR Amount:

EUR Amount = [SalesYTD] * [AdjustedRate]
 
And display them in matrix visual. 
 
Example 1: Reporting month = February. Everything works fine
Matrix_February.jpg
 
Example 2: Reporting month = March.
Because there are no sales data in March from India in INR, the AdjustedRate is also filtered out, though the INR Exchange rate exists for March in Exchnage Rate table. As a result, the EUR Amount is not calculated, although the SalesYTD value is > 0.
Matrix.jpg
 

I need to find a way to apply the INR rate from March, and thus calculate the EUR Amount for India.

 

I was trying differnet combinations of FILTER on AdjustedRate measure, but none seem to work, or maybe I am doing something wrong.

 

Your help will be highly appreciated!

 

Thank you in advance!

PS: here is the link to PBIX file:
https://drive.google.com/file/d/1BVDWF1Y23jZhNwMnqHPcPvLpnPusclho/view?usp=sharing 

0 REPLIES 0

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors