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 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