sales amount calculation by using exchange rate table with historic values

[bsz412]

Hi, 

I have to calculate sales amount for multiple countries. The exchange rate table looks like this: 

In the report I have this filter: 

Where the end period is coming from the 'Endperiod' table, as highlighted in yellow.

The request is to calculate local sales amounts to EUR (that column is in the sales_purchase table). The exchange rate should always be the exchange rate of the selected "End Period". So if it is February, all sales, even 2021 Jan should be calculated on FEB 2021 ex rate.

These are my tables: 

I cannot have now a 1 to many relationship between exchange rate and sales_purchase table, so cannot use a simple SUMX and RELATED to calculate everything to EUR. 

Can you please help me how I could do this?

[bsz412]

@johnt75 , I have another page in this report where time selection follows a different logic

here the year slider is coming from the endperiod table year column. When it is filtered like this, the expected output should show Feb-Apr 2022 data. But now as end period table is filtered on 2 years, selectedvalue does not work.... 

is it possible to create a version of the meausre above that gives this expected output?

thanks so much, I really appreciate your help

[bsz412]

Sales Amount EUR =
SUMX (
    sales_purchase,
    VAR exchRate =
        LOOKUPVALUE (
            exchange_rate[avg_rate],
            exchange_rate[country_code], sales_purchase[country_code],
            exchange_rate[Year], MAX( EndPeriod[Year] ),
            exchange_rate[Month], SELECTEDVALUE ( EndPeriod[Month] )
        )
    RETURN
        sales_purchase[sales_amount] * exchRate

[bsz412]

if I just replace selectedvalue to MAX, it will do the job, what do you think?

[johnt75]

Yes, that should work

[johnt75 · Accepted solution]

Try

Sales Amount EUR =
SUMX (
    sales_purchase,
    VAR exchRate =
        LOOKUPVALUE (
            exchange_rate[avg_rate],
            exchange_rate[country_code], sales_purchase[country_code],
            exchange_rate[Year], SELECTEDVALUE ( EndPeriod[Year] ),
            exchange_rate[Month], SELECTEDVALUE ( sales_purchase[Month] )
        )
    RETURN
        sales_purchase[sales_amount] * exchRate
)

[bsz412]

OMG it is working, just one tiny correction: 

Sales Amount EUR =
SUMX (
    sales_purchase,
    VAR exchRate =
        LOOKUPVALUE (
            exchange_rate[avg_rate],
            exchange_rate[country_code], sales_purchase[country_code],
            exchange_rate[Year], SELECTEDVALUE ( EndPeriod[Year] ),
            exchange_rate[Month], SELECTEDVALUE ( EndPeriod[Month] )
        )
    RETURN
        sales_purchase[sales_amount] * exchRate
)