Forum Discussion

Puja's avatar
Puja
Helper III
3 years ago
Solved

Dynamic currency conversion help

Hello Experts, I need some help with Dynamic currency conversion. Here is my request I have a Fact table with three dates with three amounts(below screenshot). When the user selects a currency c...
  • johnt75's avatar
    johnt75
    3 years ago

    try

    Invoiced net sales converted =
VAR SelectedCurrency =
    SELECTEDVALUE ( 'Exch rate'[Currency] )
RETURN
    IF (
        NOT ISBLANK ( SelectedCurrency ),
        SUMX (
            'Table',
            VAR ReferenceDate = 'Table'[Invoice date]
            VAR ReferenceAmount = 'Table'[Invoiced net sales]
            VAR ExchRate =
                CALCULATE (
                    MAX ( 'Exch rate'[_EXCH_RATE] ),
                    'Exch rate'[Currency] = SelectedCurrency,
                    'Exch rate'[Date] = DATE( YEAR( ReferenceDate), MONTH(ReferenceDate),1)
                )
            RETURN
                ExchRate * ReferenceAmount
        )
    )
Puja's avatar
Puja
Helper III
3 years ago

Hi johnt75 , 

Thanks for the solution.

I tested your solution and it looks like its only reading data on 1st of each month.

In exchange table , I have ave rate by month.

Ex:  date             Exch  AVE rate

     6/1/2020       0.12

     7/1/2020       0.09

     8/1/2020       1.19

 

 

Example : 6/1/2020  Converting correctly.

                6/2/2020 I see blank althought there is data.

 

Could you please help.

Thank you SO MUCH

johnt75's avatar
johnt75
Super User
3 years ago

try

Invoiced net sales converted =
VAR SelectedCurrency =
    SELECTEDVALUE ( 'Exch rate'[Currency] )
RETURN
    IF (
        NOT ISBLANK ( SelectedCurrency ),
        SUMX (
            'Table',
            VAR ReferenceDate = 'Table'[Invoice date]
            VAR ReferenceAmount = 'Table'[Invoiced net sales]
            VAR ExchRate =
                CALCULATE (
                    MAX ( 'Exch rate'[_EXCH_RATE] ),
                    'Exch rate'[Currency] = SelectedCurrency,
                    'Exch rate'[Date] = DATE( YEAR( ReferenceDate), MONTH(ReferenceDate),1)
                )
            RETURN
                ExchRate * ReferenceAmount
        )
    )
  • Puja's avatar
    Puja
    Helper III
    3 years ago

    johnt75 ,

    This is working GREAT. Thank you SO MUCH.
    I want to ask for some more help. How can I use this same measure with the USERELATIONSHIP function? I have two other dates looking at different amounts in the same fact table. I did try, but it's not working for me.

    THANKS AGAIN 🙂

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      You'd need to change the columns in the ReferenceDate and ReferenceAmount columns. If you need to be able to chart it against the different dates you might wrap the whole thing inside CALCULATE( ..., USERELATIONSHIP('Table2'[Date],'Date'[Date]))

      • Puja's avatar
        Puja
        Helper III
        3 years ago

        Hi johnt75 

        Yes, I created the way you discribed.

        VAR ReferenceDate = CALCULATE(VALUES('FACT'[ DATE]), USERELATIONSHIP('DIMDATE'[CalendarDate], 'FACT'[DATE]))

        Its not working.