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'[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.

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        I meant something like

        Using different date =
CALCULATE (
    VAR SelectedCurrency =
        SELECTEDVALUE ( 'Exch rate'[Currency] )
    RETURN
        IF (
            NOT ISBLANK ( SelectedCurrency ),
            SUMX (
                'Table',
                VAR ReferenceDate = 'Table'[Date 2]
                VAR ReferenceAmount = 'Table'[Different amount]
                VAR ExchRate =
                    CALCULATE (
                        MAX ( 'Exch rate'[_EXCH_RATE] ),
                        'Exch rate'[Currency] = SelectedCurrency,
                        'Exch rate'[Date] = DATE ( YEAR ( ReferenceDate ), MONTH ( ReferenceDate ), 1 )
                    )
                RETURN
                    ExchRate * ReferenceAmount
            )
        ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[Date 2] )
)