Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello DAX wizzards!
Hello @OwenAuger !
I am trying to solve a combined issue presented by Alberto Ferrari in YouTube
SQLBI topic - Show the initial balance for any date selection in Power BI
https://www.youtube.com/watch?v=evLjIl2QBpg
Running Total multiple currency conversion to one
I attached a pbix model. I have to create a visual - namely Balance Sheet, that will show Open Balance at the closing period prior to reporting, e.g. if we show Balance Sheet for 2019 fixcal year, the Open Balance is to be revalued at FX rate for the selected currency as of 31 Dec 2018... for which I tried to introduce
VAR OpenBalanceDate = CALCULATE(EOMONTH( MIN ( 'dimDates'[Date Key] ),-1), ALLSELECTED())
**just to explain what I am trying to get to....
However, I guess I cannot make it work through filter and row context to push the FX rate to be picked up in "Open Balance"
I named the measure I am working on Balance_EUR (work in progress)
Will you please have a look?
Dropbox with the pbix file
https://www.dropbox.com/scl/fi/t9gc7o2bl86kjfvsh3oud/RunningTotalCurrency_Conversion_v3.pbix?rlkey=k...
Solved! Go to Solution.
Hi @MiraAbel,
I have created a measure to calculate the exchange rate for the opening balance with this DAX code:
Exchange Rate =
VAR CurrentMonth = SELECTEDVALUE( BalanceDate[Month Name] )
VAR CurrentCurrency = SELECTEDVALUE( dimCurrency[CurrencyId] )
VAR BeginningDate =
CALCULATE( MIN( dimDates[Date Key] ), ALLSELECTED() )
VAR PreviousDate = ENDOFMONTH( PREVIOUSMONTH( DATESINPERIOD( BalanceDate[Date Key], BeginningDate, -1, MONTH ) ) )
VAR DenominatorDate = IF(CurrentMonth = "Open balance", PreviousDate, LASTDATE( dimDates[Date Key] ) )
VAR ExchangeRate = LOOKUPVALUE( dimCurrExchange[Exchange Rate], dimCurrency[CurrencyId], CurrentCurrency, dimCurrExchange[DateKey], DenominatorDate )
RETURN
ExchangeRate
My first thought was to know what date is the opening balance, so I calculated the VAR PreviousDate above.
Then, to look for the exchange rate of that date I'm using the VAR ExchangeRate above with the function LOOKUPVALUE with date and currency id. Then when we don't have a date but a label "Open balance" I used the VAR DenominatorDate to use the PreviousDate instead. This returns the correct exchange rate for every row.
Finally, I modified your measure "Balance_EUR (work in progress) like this:
Balance_EUR (work in progress) =
VAR CurrentMonth = SELECTEDVALUE ( BalanceDate[Month Name] )
VAR Running_EUR =
--solved!
SUMX (
dimCurrency,
DIVIDE(
[Running Total_LCurr],
CALCULATE (
VALUES ( dimCurrExchange[Exchange Rate] ),
LASTDATE ( dimDates[Date Key])
)
)
)
VAR DateStart = CALCULATE ( MIN ( dimDates[Date Key] ), ALLSELECTED () )
VAR OpenBalanceDate = CALCULATE(EOMONTH( MIN ( 'dimDates'[Date Key] ),-1), ALLSELECTED())
VAR OpenBalance = CALCULATE ( [Sum_Transaction_LCurr], dimDates[Date Key] < DateStart )
--OpenBalance is still work in progress
VAR IsGrandTotal =
COUNTROWS ( dimDates ) = CALCULATE ( COUNTROWS ( dimDates), ALLSELECTED( ) )
VAR OpenBalanceConversion =
DIVIDE(
OpenBalance,
[Exchange Rate]
)
VAR Result =
IF (
CurrentMonth = "Open balance",
OpenBalanceConversion,
-- 100 is placefolder for OpenBalance
IF (
IsGrandTotal, Running_EUR + OpenBalanceConversion, Running_EUR
)
)
RETURN
Result
Where I added the VAR OpenBalanceConversion to make the conversion of the open balance in local currency, which I use in the final IF statement when the CurrentMonth is "Open balance".
I hope it works for you and I'm glad I could help 😜
Cheers,
Fernando
P.S. Here's the pbix file.
Hi @MiraAbel,
I have created a measure to calculate the exchange rate for the opening balance with this DAX code:
Exchange Rate =
VAR CurrentMonth = SELECTEDVALUE( BalanceDate[Month Name] )
VAR CurrentCurrency = SELECTEDVALUE( dimCurrency[CurrencyId] )
VAR BeginningDate =
CALCULATE( MIN( dimDates[Date Key] ), ALLSELECTED() )
VAR PreviousDate = ENDOFMONTH( PREVIOUSMONTH( DATESINPERIOD( BalanceDate[Date Key], BeginningDate, -1, MONTH ) ) )
VAR DenominatorDate = IF(CurrentMonth = "Open balance", PreviousDate, LASTDATE( dimDates[Date Key] ) )
VAR ExchangeRate = LOOKUPVALUE( dimCurrExchange[Exchange Rate], dimCurrency[CurrencyId], CurrentCurrency, dimCurrExchange[DateKey], DenominatorDate )
RETURN
ExchangeRate
My first thought was to know what date is the opening balance, so I calculated the VAR PreviousDate above.
Then, to look for the exchange rate of that date I'm using the VAR ExchangeRate above with the function LOOKUPVALUE with date and currency id. Then when we don't have a date but a label "Open balance" I used the VAR DenominatorDate to use the PreviousDate instead. This returns the correct exchange rate for every row.
Finally, I modified your measure "Balance_EUR (work in progress) like this:
Balance_EUR (work in progress) =
VAR CurrentMonth = SELECTEDVALUE ( BalanceDate[Month Name] )
VAR Running_EUR =
--solved!
SUMX (
dimCurrency,
DIVIDE(
[Running Total_LCurr],
CALCULATE (
VALUES ( dimCurrExchange[Exchange Rate] ),
LASTDATE ( dimDates[Date Key])
)
)
)
VAR DateStart = CALCULATE ( MIN ( dimDates[Date Key] ), ALLSELECTED () )
VAR OpenBalanceDate = CALCULATE(EOMONTH( MIN ( 'dimDates'[Date Key] ),-1), ALLSELECTED())
VAR OpenBalance = CALCULATE ( [Sum_Transaction_LCurr], dimDates[Date Key] < DateStart )
--OpenBalance is still work in progress
VAR IsGrandTotal =
COUNTROWS ( dimDates ) = CALCULATE ( COUNTROWS ( dimDates), ALLSELECTED( ) )
VAR OpenBalanceConversion =
DIVIDE(
OpenBalance,
[Exchange Rate]
)
VAR Result =
IF (
CurrentMonth = "Open balance",
OpenBalanceConversion,
-- 100 is placefolder for OpenBalance
IF (
IsGrandTotal, Running_EUR + OpenBalanceConversion, Running_EUR
)
)
RETURN
Result
Where I added the VAR OpenBalanceConversion to make the conversion of the open balance in local currency, which I use in the final IF statement when the CurrentMonth is "Open balance".
I hope it works for you and I'm glad I could help 😜
Cheers,
Fernando
P.S. Here's the pbix file.
Hello, Fernando
This measure [Exchange Rate] using LOOKUPVALUE you've suggested does the job! I suggest we wait whether anyone else comes back with other options. Thank you for looking into this.
Happy DAXing!
Mira
Hi, @MiraAbel
I can't open the link you shared:
Can you share your PBIX file in another way, such as OneDrive , and if you're not sure how to upload it, you can click on the link below:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards
Jianpeng Li
Hello v-jianpeng-msft,
Thank you for looking into this - please try this link
SharedPowerBI
Otherwise, can you please try the link provided by Fernando as that is the original pbix with a measure that Fernando suggested [Exchange Rate]? My original (play with) measure is as shown below:
Balance_EUR (work in progress) =
VAR CurrentMonth = SELECTEDVALUE ( BalanceDate[Month Name] )
VAR Running_EUR =
--solved!
SUMX (
dimCurrency,
[Running Total_LCurr]
* CALCULATE (
VALUES ( dimCurrExchange[Exchange Rate] ),
LASTDATE ( dimDates[Date Key])
)
)
VAR _currency = SELECTEDVALUE(fctTable[CurrencyId])
VAR DateStart = CALCULATE ( MIN ( dimDates[Date Key] ), ALLSELECTED () )
VAR OpenBalanceDate = CALCULATE(EOMONTH( MIN ( 'dimDates'[Date Key] ),-1), ALLSELECTED())
VAR _rate =
CALCULATE (
MAX ( dimCurrExchange[Exchange Rate] ),
FILTER (
ALL ( dimCurrExchange ),
dimCurrExchange[CurrencyId] = _currency
&& dimCurrExchange[DateKey] = OpenBalanceDate
)
)
VAR OpenBalance = CALCULATE ( [Total_LCurr], dimDates[Date Key] < DateStart )
--VAR _rate = CALCULATE(
--OpenBalance is still work in progress
VAR IsGrandTotal =
COUNTROWS ( dimDates ) = CALCULATE ( COUNTROWS ( dimDates), ALLSELECTED( ) )
VAR Result =
IF (
CurrentMonth = "Open balance",
100 ,
-- 100 is placefolder for OpenBalance
IF (
IsGrandTotal, Running_EUR + 100, Running_EUR
)
)
RETURN
Result
Kind regards,
Mira
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |