Initial Balance converted at specific rate - Running Total multiple currency conversion

[MiraAbel]

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

https://community.fabric.microsoft.com/t5/Desktop/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... 

[calerof · Accepted 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.

[MiraAbel]

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

[v-jianpeng-msft]

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

[MiraAbel]

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