Exchange Rates Calculated Column

[fernandoC]

Hi Team,

I'm trying to create a calculated column that takes a value from a column and divides it based on a specific exchange rate that is located on a different table, the issue that I've been having is that I need that the convertion to be aligned based on a Location (Target DU) and to match the date in both tables as some exchange rates vary month to month. 

Please find below some sample data: 

This is an exchange table created only with the Currency Names and the exchange rate. The date will define that these values are only valid for November 2020. 

These columns are coming from a fact table. 

[v-alq-msft]

Hi, @fernandoC 

If you take the answer of someone, please mark it as the solution to help the other member0s who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[fernandoC]

Hi @v-alq-msft & @lbendlin ,

Thank you very much for your solutions. Sorry I didn't respond earlier. 

At the end I resolved this issue by creating small dimension tables each with date columns and the exchange rate of each currency. Then I created a calculated column on my fact table with nested if's so that depending on the location and the date it would take a value of an specific dimension table. 

Thanks again!! 

[v-alq-msft]

Hi, @fernandoC 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Curreny:

Fact:

You may create a calculated column as below.

Result = 
DIVIDE(
    [Salary Expectations],
    CALCULATE(
        SUM('Currency'[Value]),
        FILTER(
            ALL('Currency'),
            [Attribute]=EARLIER('Fact'[Target DU.1])&&
            [Date]=EARLIER('Fact'[RESOLUTIONDATE])
        )
    )
) 

Result:

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[lbendlin]

1. Unpivot your currency table so that you have three columns

Date | Currency | value

2. Create a composite key 

YearMonth + Currency

3. in your fact table create the same composite key

4. link your tables via the composite key.

5. create your measure as needed.