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
        )
    )
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] )
)