Create column calculated exchange

[Anonymous]

Hi members , i want to create a mesure In DAX that exchanges currency i have two tables first table (Transaction) with the columns : Customer_id transaction_date , transaction_amount second Table ( Exchange) with the columns : Customer_id , datre_from , date_to , exchange rate . i need to have a new column named exchanged_amount with the three conditions : [client_id].Transaction = [client_id].Exchange AND date_from <= transaction_date AND transaction_date <= end_date like this example : Transaction Id transaction Transaction_date Client_id Currency Transaction_amount I47028345098 12-05-2015 DD USD 100 Exchange_ client _d date_from date_to exchange_rate DD 01_05_2015 30_05-2015 1.37 Transaction Id transaction Transaction_date Client_id Currency Transaction_amount Exchanged_amount I47028345098 12-05-2015 DD CAD 100 137 i tried calculate with product x and filter but no success need help thank you

[AkhilAshok · Accepted solution]

You could try a measure like this:

Transaction Amount Exch =
SUMX (
    Transaction,
    VAR Customer = Transaction[Customer_id]
    VAR Dt = Transaction[transaction_date]
    VAR Amt = Transaction[transaction_amount]
    VAR ExchRate =
        MAXX (
            FILTER (
                Exchange,
                Exchange[Customer_id] = Customer
                    && Exchange[date_from] <= Dt
                    && Exchange[date_to] >= Dt
            ),
            Exchange[exchange_rate]
        )
    RETURN
        Amt * ExchRate
)

[Anonymous]

That works , thanks a lot Bro