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 

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