Forum Discussion

MiKeZZa's avatar
MiKeZZa
Icon for Post Patron rankPost Patron
5 years ago
Solved

Currency conversion on choosen reporting date

Hi all,   I'm looking for a solution for my DAX issue. I have a star schema with a facttable which contains amounts in several currencies. I want to pick a date in the report and then the amounts m...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi MiKeZZa ,

     

    First of all you need to have all you ReportingDateID as Whole numbers

     

    Try the following measure, if this is not the correct result please tell me so I can revise:

    Sales Currency = 
    VAR SelectedCurrency =
        SELECTEDVALUE ( 'Currency'[Code] )
    VAR DatesExchange =
        SUMMARIZE (
            Rates,
            ReportingDate[Month year],
            Rates[Rate]
        )
    VAR Result =
                SUMX (
                    DatesExchange,
                    DIVIDE([AmountSum] , Rates[Rate])
                )
       
       
    RETURN
        Result

     

    PBIX file attach.

MiKeZZa's avatar
MiKeZZa
Icon for Post Patron rankPost Patron
5 years ago

Really really great MFelix! Thanks a lot.

 

For my understanding; is DatesExchange a temporary table based on Rates, but does it get a place in the model with the relations as defined for Rates? Or what's the trick here? How can I make a table with Rates and use it in the divide function and be sure that it gets the right currency (which it does actually)?