How to handle multiple currencies DAX

[RK_JOB]

Hello,

I am opening a new thread for more clarity.

Problem statement: We want to find out the Avg. salaries in dollars for people in same position in different part of the organization (which could be in multiple countries).

From the month slicer, I need to select multiple months so I cover everyone till that moment. I have to use the latest month's exchange rate for those currencies.

For e.g.: Here I selected till Apr 2022. Table 1 is good and the dollar amount is good too in the last column.

Only problem is table #2. If I do a pivot table from table 1 then answer should be $1,74,3198. How do I get that?

Here is the output I am looking for.

Here is the Pbix: there is a download button:
https://drive.google.com/file/d/1zAY0UeG3ESVpB6WBwkKbinJeLfiE6TfD/view?usp=share_link

Here is the pivot table output: https://drive.google.com/file/d/1Ub9f8NGT3_GMbAcME8joKNR_xHLhUqiB/view?usp=share_link

Again Thanks a lot in adavance.

-EZ

[kunal15sep]

1. Use a disconnected table to store currency code & conversion rate.
2. Use LookUpvalue to calculate the equivalent amount in required currency via calculated column.

I hope this answers your requirement.

[RK_JOB]

Can you please elaborate. Have you had chance to look at the model?It won't work that way. If you look at the model, I need to look for the latest value based off of month and then sum it up the dollar amount.

[kunal15sep]

Please see the below codes for calculated column:

Considering exchange rate for a particular point in time:

currencyConversion = LOOKUPVALUE(HRIS_MD_EXCHANGE_RATES[Conversion Exchange Rate],HRIS_MD_EXCHANGE_RATES[DistinctYearmonthCurrency ],HRIS_TD_COMP_WAGE_DETAIL_NEWOLD[Distinct Year Month Currency])

ForexAdjustedSalary = HRIS_TD_COMP_WAGE_DETAIL_NEWOLD[Salary in Loc. Currency] * HRIS_TD_COMP_WAGE_DETAIL_NEWOLD[currencyConversion]

Considering latest value as of today you'll need to fetch value for YEAR(DATE) & DAY(DATE) & LOCALCURRENCY

The disconnected table will have just latest date & associated key to retrieve data.