Currency conversion based on monthly rates by group

[Anonymous]

Hi,

I think i have check most solution on currency conversion but hardly can find something that suited my needs. Just to give some backgroud, what I intend to do is have a sales data converted (table data are linked from 1 table down to FX table) based on what group it resides (organization) and what month is it in. 

Here is the data model structure

Where the revenue is the sales data. And here is my fx data which i have unpivoted to have the rates breakdown by month like this :

Now when i pull the data just to view it shows the sum of it instead of the absolute amount.

My question is why does it happen like this? is it an issue with the data model link? Question number 2 is how do i make the conversion based on group by month so the revenue are converted based on the monthly rates?

Thanks in advance.

[amitchandak]

@Anonymous , Many to Many between project/revenue can that be made 1-M ?

[Anonymous]

I dont quite get your explaination - is it a question or?

[lbendlin]

1. You need to link your calendar table directly to the fact table (revenue), not via the conversion table.

2. Create a composite key MonthCurrencyCode both in the conversion table and in the fact table. Then link these tables via the new key, 1:* from conversion to revenue.

[Anonymous]

Thanks for the response. I manage to get that working

2. How do do this? I changed the model to become like this? Looks like a loop.