Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

currency conversion

Hi everyone,

 

Can someone help me with a currency conversion

 

Marco Russo makes this article: 

https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/ 

 

nice to see that the currency problem is solved with a summarize virtual table

 

Question 1..

In my situation we have a lot of different companys in the dataset. So its possible that i have salesmutations in EUR and AUD etc 

How can i make this work to convert it to a reporting currency

 

Question 2.

The Exchangetable have for each company a rate from Transaction Currency to Reporting Currency. But not for everyday, but only when the rate changes. 

example.. i need the last exchange rate that exist for a company and transaction / reporting currency 

 

Question 3.

When we make a summery table like the example from Marco Russo. How works the relation between the virtual table and the date table and company table? Is t

 

With kind regards.....

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    This is the formula that i uses:

    Amount in report currency =

VAR report_valuta =
    SELECTEDVALUE ( 'Reporting Currency'[Reporting Currency] )
VAR last_datum =
    LASTDATE ( Kalender[Date] )
VAR temp_Exchange =
    SUMMARIZE (
        ExchRates,
        ExchRates[Administratie],
        "temp_koers",
            MAXX (
                TOPN (
                    1,
                    FILTER (
                        ExchRates,
                        ExchRates[Date] <= last_datum
                            && ExchRates[Transaction Currency] = report_valuta
                    ),
                    ExchRates[Date], DESC
                ),
                ExchRates[Factor gedeeld]
            )
    )
RETURN
    IF (
        HASONEVALUE ( 'Reporting Currency'[Reporting Currency] ),
        SUMX ( temp_Exchange, [temp_koers] * [Bedrag Native Currency totaal] )
    )

     

     

    • v-xuding-msft's avatar
      v-xuding-msft
      Community Support
      5 years ago

      Hi Anonymous ,

       

      I find that you have attached your formula here. Have you resolved the problems? If yes, you could accept your answer as solution. Others will benefit from the answer. If you still need help, can you please share some sample data and expected results with us?  We are not clear about your situation just based on the description and the formula. Thanks.