Forum Discussion

bsz412's avatar
bsz412
Icon for Helper III rankHelper III
4 years ago
Solved

sales amount calculation by using exchange rate table with historic values

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?

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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
)

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      bsz412
      Icon for Helper III rankHelper III
      4 years ago

      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
)
  • bsz412's avatar
    bsz412
    Icon for Helper III rankHelper III
    4 years ago

    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's avatar
      bsz412
      Icon for Helper III rankHelper III
      4 years ago
      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's avatar
        bsz412
        Icon for Helper III rankHelper III
        4 years ago

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