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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.