currency conversion

[Anonymous]

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.....

[Anonymous]

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]

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.

[Greg_Deckler]

@Anonymous - Recipe 8 of Chapter 5 of DAX Cookbook has a section on currency exchange rates. You cand download the PBIX here:

https://github.com/gdeckler/DAXCookbook

See if that helps, otherwise, please post sample data as text and expected output.