Forum Discussion

ScR's avatar
ScR
Frequent Visitor
9 years ago
Solved

Many to one currency conversion

I am in need of some guidance. I am looking to creating a Many-to-One conversion for our sales reports but I can´t seem to get it right. I am way out of my depth here but I would love to get some ...
Need Help
  • kschaefers's avatar
    kschaefers
    9 years ago

    This, although it may seem simple is a considerably more difficult task.

     

    You can use this formula:

     

    Total (Base Currency) =
 (
    SUMX (
        Sales,
        VAR current_date = Sales[Date]
        RETURN
            Sales[Amount]
                * CALCULATE (
                    CALCULATE (
                        VALUES ( dimExchange[Exchange Rate] ),
                        LASTDATE ( dimExchange[Date Key] )
                    ),
                    FILTER ( ALL ( dimCalendar ), dimCalendar[Date Key] <= current_date )
                )
    )
)

    As you can see this is significantly more difficult than the other solution :)

    The code basically iterates through each row of the Sales table, transforms the Row Context into a filter context (to filter the Currency on the DimExchange Table) and then modifies the dimCalendar filter to select all dates prior to the current date (in the iterated Sales table row). Finally it chooses the last date available for the given Filter set from the DimExchange table.

     

    It took me a while to wrap my head around the whole Filter and Row Context topic as well as context transition, and I think I am just scratching the surface, so maybe there is a more elegant solution here :)

kschaefers's avatar
kschaefers
Icon for Resolver I rankResolver I
9 years ago

Hi ScR,

 

simplest solution would be to use:

Total (Base Currency) = SUMX(dimExchange,[Total]*dimExchange[Exchange Rate])

 You get the following:As you can see there are no exchange rates for most of the days, but I assume this is beacuse it is sample data

  • ScR's avatar
    ScR
    Frequent Visitor
    9 years ago

    Thanks kschaefers.

     

    Yes and no on the lack of data. Is it possible to have all the sales data between the available exchange rate dates use the previous data point for conversion? Am I going about this the wrong way?

     

    The problem is that there will probably not be exchange rate data for all the dates.

     

     

    • kschaefers's avatar
      kschaefers
      Icon for Resolver I rankResolver I
      9 years ago

      This, although it may seem simple is a considerably more difficult task.

       

      You can use this formula:

       

      Total (Base Currency) =
 (
    SUMX (
        Sales,
        VAR current_date = Sales[Date]
        RETURN
            Sales[Amount]
                * CALCULATE (
                    CALCULATE (
                        VALUES ( dimExchange[Exchange Rate] ),
                        LASTDATE ( dimExchange[Date Key] )
                    ),
                    FILTER ( ALL ( dimCalendar ), dimCalendar[Date Key] <= current_date )
                )
    )
)

      As you can see this is significantly more difficult than the other solution :)

      The code basically iterates through each row of the Sales table, transforms the Row Context into a filter context (to filter the Currency on the DimExchange Table) and then modifies the dimCalendar filter to select all dates prior to the current date (in the iterated Sales table row). Finally it chooses the last date available for the given Filter set from the DimExchange table.

       

      It took me a while to wrap my head around the whole Filter and Row Context topic as well as context transition, and I think I am just scratching the surface, so maybe there is a more elegant solution here :)

      • ScR's avatar
        ScR
        Frequent Visitor
        9 years ago

        I have to dive in to that and see if I understand it correctly.

         

        Thanks a lot for your time kschaefers