Show Value of Exchange Rate based on MAX Month

DAX DAX Calculations
1

Hello,

I have another query on Exchange Rates, not sure how to resolve it…This has been amazing learning experience…can’t thank enough to @Harsh

Sorry, for bombarding so many questions @Harsh

I have 3 tables:

  1. Fact table
  2. Date table
  3. Currency Rate Table

The problem is : When user selects the slicer which has Year and month (coming from date table), I need to show Exchange rate for that month but it shows the exchange rate when the user was hired.

like for Hong Kong Dollar, it should be .12747 for March 2019
and for Chinese Currency, it should be .14778 for March 2019.

image
image743×548 20.6 KB

image
image832×216 9.85 KB

image
image872×244 6.95 KB

But I am getting this:

for China, it should have been .14778 and for Hong Kong it should have been .12747.

image
image784×254 28.9 KB

The only reason row 4 is correct is because that guy was hired in March 2019.
But I don’t care about when was he hired. I want to show the latest conversion exchange rate selected by the year month (in this case March 2019) so I can multiply it correctly to get dollar value.

I created a Dollar table below. As Hong Kong person is only available in Jan, I am getting wrong dollar value for him.

image

It should have been: .12747*219,697 = 28004

I know the reason, why those exchange rates are coming as I have connected the main table with exchange rate table…but how do I connect the date table with Exchange Rate table without affecting anything else?

PBIX: https://drive.google.com/file/d/1s3QNX24wEc3_7Ps1ER8vlLhJWibS34sB/view?usp=share_link

Thanks in advance,
EZ

1 Like
2

Hi @rit372002,
Thanks you very much for post your query into then forum.
You can try the following measures to see if it is what you are looking for:

Exc. Rate by Month and Year =

VAR _CurrentLocalCurrency = MAX( 'Main Table'[Local Currency] )

VAR _SelectedYearMonth = MAXX('My Date Table',

     [YearMonth] )

VAR _DistinctYearmonthCurrency = _SelectedYearMonth & _CurrentLocalCurrency

VAR _Result = CALCULATE(

    MAXX(

        HRIS_MD_EXCHANGE_RATES,

        HRIS_MD_EXCHANGE_RATES[Conversion Exchange Rate]

    ),

    HRIS_MD_EXCHANGE_RATES[DistinctYearmonthCurrency ] = _DistinctYearmonthCurrency

)

RETURN _Result

Avg. Jan 1 Salary - Total Conversion in $ - Correct Sum by Month and Year =

        SUMX (

            ADDCOLUMNS (

                SUMMARIZE (

                    'Main Table',

                   

                    'Main Table'[Global Manager_level_text],

                    'Main Table'[Local Currency]

                   

                ),

                "@Totals",

                    [AVG - Jan 1st EE Avg. Salary Loc. Currency M] * [Exc. Rate by Month and Year]

            ),

            [@Totals]

        )

image
image895×501 28 KB

Regards

Enterprise - Conversion - Currency_JAFP.pbix (8.2 MB)

1 Like
3

Hello @rit372002,

Did the response above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need? If it did, please mark the solution that helped you.

Thank you

5

Hi @rit372002 ,

Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Thanks @jafernandezpuga for providing detailed solution for this post.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

1 Like