Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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
)
@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
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
if I just replace selectedvalue to MAX, it will do the job, what do you think?
Yes, that should work
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
)
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
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |