cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joanne
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

Untitled.png

 

Sample Data

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

 

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

 

MayMay RateJunJun 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

CurrencyMonthEndDateRateCurrencyKey
AUD5/31/2021 0:001.291AUD^202105
NZD5/31/2021 0:001.369NZD^202105
USD5/31/2021 0:001USD^202105
AUD6/30/2021 0:001.319AUD^202106
NZD6/30/2021 0:001.416NZD^202106
USD6/30/2021 0:001USD^202106

 

I appreciate any help. Thanks!

4 REPLIES 4
Joanne
Frequent Visitor

Hi @Greg_Deckler, thanks for your reply. 

 

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. 

@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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi @Greg_Deckler Link to PBIx: Link 

 

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. 

 

Joanne_1-1632455931732.png

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

 

Joanne_2-1632456291791.png

 

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])

 

Thank you in advance. 

 

 

 

Greg_Deckler
Super User
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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.