Forum Discussion

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

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

v-xiaotang

Community Support

4 years ago

Hi Sai_Alkesh

Thanks for reaching out to us.

Here are some questions to be confirmed:

"The issue is, i get the calculated column values, where the period is 20214, but not for other rows, where the period is not '20214'. Values in Red font in the calculated column are not getting calculated in PBI."

what are the correct values for other rows? Currency & Period of row1/row2/row3 are different, will they affect calculation results? If yes, for row2/3, how to calculate ( Math formula)?

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

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