Forum Discussion

Puja's avatar
Puja
Icon for Helper III rankHelper 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
Icon for Helper III rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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
      Icon for Super User rankSuper 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] )
)