Need to divide filtered product information by average forex rate

[eddibu]

Could you please help me:

I have two tables:

Table 1 (Daily Fx rates for many currencies for each day of the month)

Table 2 (Product metrics)

 What I need:

1. Calculate average Exchange rate for each currency for the month from table 1

2. Divide "amount_orig_currency" by result from step 1 and do it only for items with Income in column Type

Regards,

Ed

[v-jtian-msft]

Hello,@eddibu 
I am glad to help you.

According to your description you want to get the filtered product information divided by average foreign exchange rate

If my understanding is correct, you can refer to my test below

Here is my test data:

1. Calculate the average exchange rate for each currency for the month:

C_AvgExchange =
VAR month_NUM =
    MONTH ( 'Daily Fx rates'[Date] )
VAR year_Num =
    YEAR ( 'Daily Fx rates'[Date] )
VAR currency_value = 'Daily Fx rates'[Currency]
RETURN
    CALCULATE (
        AVERAGE ( 'Daily Fx rates'[Exchange  rate] ),
        FILTER (
            ALL ( 'Daily Fx rates' ),
            MONTH ( 'Daily Fx rates'[Date] ) = month_NUM
                && 'Daily Fx rates'[Currency] = currency_value
                && YEAR ( 'Daily Fx rates'[Date] ) = year_Num
        )
    )

2. Divide "amount_orig_currency" by the result of C_AvgExchange and divide only items with income in the column type (Type="Income")

C_result =
IF (
    'Product metrics'[Type] = "Imcome"
        && MONTH ( 'Product metrics'[Date] ) = MAX ( 'Daily Fx rates'[Month_Num] )
        && YEAR ( 'Product metrics'[Date] ) = MAX ( 'Daily Fx rates'[Year_Num] ),
    DIVIDE (
        'Product metrics'[amount_orig_currency],
        SUM ( 'Daily Fx rates'[C_AvgExchange] ),
        0
    )
)

The result is as follows:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[eddibu]

Hello @v-jtian-msft , I appreciate your help.

As you can see In result table [C-result], is not applied for each [Income]. Some of them have blank [C_result] in top of you result screenshot. 

Regards,

Ed

[v-jtian-msft · Accepted solution]

@eddibu 
Thank you for your replay.

In the test data I used for testing, the Daily Fx rates table only has data for January and February.

In the Product metrics table, there are data for January, February and March, but the data for March in the Product metrics table (i.e. the data with a null C_Result) does not have a corresponding data in the Daily Fx rates table.

Therefore, the C_Result for March is null, not because of a Dax code error, but because the data used for testing is not accurate enough.

You can check the .pbix file I uploaded, I hope it will help you!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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