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

    Hi MFelix,

     

    I tried to get it working with the SQLBI method, but couldn't get it working. Be aware that I can't change many things on the existing model.

     

    Editing the OP is giving HTML errors, so here is a new link; download. Does this work?

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      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

        Hi MFelix,

         

        That's really close to what I want!

         

        I did change the measure in the Divide and that's great for every line. Except the total line. I've tried some things, but that total line is giving me some issues. I've had a way to determine if I'm in a detailline or the totalline, with ISFILTERED. But I don't know what to do in totalline to get what I want.

         

        You also have a clue for that?

         

        I've attacted a new PBIX, with better rates and larger numbers. As you can see the total is not the sum of the lines, but the sum of all the values and then divided by a (I don't know which one) rate.

         

        PBIX can be found here