cancel
Showing results 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

Frequent Visitor

## Converting ending balance using exchange rate based on period selection

Hi,

Both Fact_GL and Dim_ExchangeRate comtains multiple MonthEndDate for a type of currency. I want to create a measure where the Rate from Dim_ExchangeRate will be picked up based on Period Selection. The rate will be used to divide the ending balances which is also based on Period Selection (Converted Amount). For example, when May-21 is selected, YTD May-21 numbers will be divided by the May-21 rate and when Jun-21 is selected, YTD Jun-21 numbers will be divided by the Jun-21 rate.

The Final Amount should also include if the Revalue Type in Fact is "Revalue", then take the Converted Amount else Base Amount.

I'm unable to create calculated column as the rate should not be stagnant so a measure is considered.

Data Model

Sample Data

 MonthEndDate Currency Trans Amount Base Amount CurrencyKey Revalue Type 5/31/2021 0:00 USD 10,000.00 10,000.00 USD^202105 Revalue 5/31/2021 0:00 USD (120,000.00) (120,000.00) USD^202105 Revalue 5/31/2021 0:00 AUD (50,000.00) (35,893.76) AUD^202105 Revalue 5/31/2021 0:00 AUD (80,000.00) (53,404.54) AUD^202105 Non-revalue 5/31/2021 0:00 NZD (200,000.00) (124,533.17) NZD^202105 Revalue 6/30/2021 0:00 AUD (5,000.00) (3,448.28) AUD^202106 Revalue 6/30/2021 0:00 NZD (30,000.00) (18,181.82) NZD^202106 Revalue (475,000.00) (345,461.56)

Final Amount for each row in Fact based on period selection:

 May May Rate Jun Jun Rate 10,000.00 1.000 10,000.00 1.000 (120,000.00) 1.000 (120,000.00) 1.000 (38,729.67) 1.291 (37,907.51) 1.319 (53,404.54) (53,404.54) (146,092.04) 1.369 (141,242.94) 1.416 (3,790.75) 1.319 (21,186.44) 1.416 (348,226.25) (367,532.18)

Dim_ExchangeRate

 Currency MonthEndDate Rate CurrencyKey AUD 5/31/2021 0:00 1.291 AUD^202105 NZD 5/31/2021 0:00 1.369 NZD^202105 USD 5/31/2021 0:00 1 USD^202105 AUD 6/30/2021 0:00 1.319 AUD^202106 NZD 6/30/2021 0:00 1.416 NZD^202106 USD 6/30/2021 0:00 1 USD^202106

I appreciate any help. Thanks!

4 REPLIES 4
Frequent Visitor

In your measure, "[CurrencyKey] = _CurrencyKey" might not be correct as it will fix the rate for each row in Fact table. What I require is if May-21 period is selected, I need YTD May-21 Trans Amount to be converted using May-21 rate and if Jun-21 period is selected, YTD May-21 + Jun-21 entries will be converted using Jun-21 rate.

Super User

@Joanne Maybe:

``````Measure =
VAR __MonthEndDate = MAX('Dim_Calendar'[MonthEndDate])
VAR __CurrencyKey = MAXX(FILTER('Fact_GL',[MonthEndDate]=__MonthEndDate),[CurrencyKey])
VAR __Rate = MAXX(FILTER('Dim_ExchangeRate',[CurrencyKey]=__CurrencyKey && [MonthEndDate]=__MonthEndDate),[Rate])
VAR __BaseAmount = MAX('Fact_GL'[Base Amount])
RETURN
__BaseAmount * __Rate``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

I have applied the suggested measure to my solution. Below are the results:

When May-21 & Jun-21 are selected in different table, the Trans Amount correctly converted using the rate of the selected month.

If Jun-21 is selected, the expected result is YTD Jun-21 balances will be converted using Jun-21 rate as below:

Can you also please help to take a look at the MeasureTotal which is not totalling the rows number?

MeasureTotal =
VAR __Table = SUMMARIZE(Fact_GL,Fact_GL[Currency],Fact_GL[CurrencyKey],Dim_Calendar[MonthEndDate],Fact_GL[Revalue Type],"__Measure",[Measure])
RETURN
SUMX(__Table,[__Measure])

Super User

@Joanne Maybe:

``````Measure =
VAR __MonthEndDate = MAX('Dim_Calendar'[MonthEndDate])
VAR __CurrencyKey = MAX('Fact_GL'[CurrencyKey])
VAR __Rate = MAXX(FILTER('Dim_ExchangeRate',[CurrencyKey]=__CurrencyKey && [MonthEndDate]=__MonthEndDate),[Rate])
VAR __BaseAmount = MAX('Fact_GL'[Base Amount])
RETURN
__BaseAmount * __Rate``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors