Forum Discussion
bsz412
Helper III
Helper IIIsales 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 'Endp...
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 )
bsz412Helper III
Helper IIIjohnt75 , 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
bsz412Helper III
Helper IIISales 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