April 12, 2016 at 3:44 am
I have a sales table with sales amount in local currency and want to report in a consolidation currency. So far so good.
But the user needs to be able to pick a specific exchange rate date and calculate the consolidated sales amount on the fly.
Typically this will be made by having a slicer in excel for the exchange rate date.
The follwing two fact tables exist
fact_sales
local_currency_code
local_sales_amount
fact_exchange_rate
source_currency_code
exchange_rate_date
exchange_rate
One date is used as default exchange_rate_date (used if no exchange_rate_date is selected).
Note that the fact_exchange_rate does not contain destination_currency_code since only one consolidation currency is used.
I have created a calculated measure:
consolidation_sales_amount = local_sales_amount*exchange_rate
that works fine as long as local_sales_amount does not consist of different curencies
I appriciate any help or hints.
Let me know if any information is missing
April 12, 2016 at 8:03 am
I would expect your fact_Sales table to have a sale date and you would use that to determine the exchange rate on the date of the sale.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 12, 2016 at 8:09 am
fact_sales does have a sales_date but in this case that date should not be used as exchange_rate_date
I should probably have mentioned that it is related to ledger transactions and fact_accounting would be a better fact table name
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply