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
        )
    )
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'[_FROM_DT] <= ReferenceDate
                        && 'Exch rate'[_TO_DT] >= ReferenceDate
                )
            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 🙂