Forum Discussion

Sai_Alkesh's avatar
Sai_Alkesh
Helper II
4 years ago
Solved

undefined

Hi, Need help with the following: I have 2 tables: Table Currency Identifier field LCurrency FCurrency FX Rate Type     Period yyyymm         EUr           GBp           0.8545    AVRS   20214 ...
Calculated columns
DAX
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Sai_Alkesh 

    try this calculated column

     

    ColumnTest = 
VAR _currency =
    CALCULATE (
        MAX ( 'Currency'[FXRate] ),
        FILTER (
            ALL ( 'Currency' ),
            'Currency'[LCurrency] = EARLIER ( 'Sales'[Currency] )
        )
    )
RETURN
    'Sales'[Sales] * DIVIDE ( 1, _currency )

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Sai_Alkesh 

    In this scenario, you can modify the red part. 

    -

    EARLIER: EARLIER function (DAX) - DAX | Microsoft Docs

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Sai_Alkesh's avatar
Sai_Alkesh
Helper II
4 years ago

Tang 

Thank you for reaching out.

The formula is Sales * DIVIDE(1, FX Rate (from currency table), where Sales.Currency=Currency.LCurrency

For row 2, the example will be 8000 * 1/1.1684 = 6846.97 and 

For row 3, the exmaple will be 2000 * 1/0.8545 = 2340.55

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi Sai_Alkesh 

try this calculated column

 

ColumnTest = 
VAR _currency =
    CALCULATE (
        MAX ( 'Currency'[FXRate] ),
        FILTER (
            ALL ( 'Currency' ),
            'Currency'[LCurrency] = EARLIER ( 'Sales'[Currency] )
        )
    )
RETURN
    'Sales'[Sales] * DIVIDE ( 1, _currency )

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.