Create Measure to show total $ based on the selected date's currency exchange rates

[Anonymous]

Hello,

I'm trying to figure out how to create the appropriate measure to create this, but stuck in a runt on how to get started.

Essentially, I'm trying to create a graph that allows you to show how total customer values change over time (based on extract date) - this is easily doable. However, the issue I'm facing is that I want to add in a date slicer so that I can see how total customer's value will change based on FX Rates on different dates.

As an example, I want to make a bar graph which will show the total AUD value for 31 Jan 2020 because I have selected 31 January 2020 based on extract date (table 1). I then want to have another date slicer (table 2), which will them allow me to view how the total AUD value will change now, calculating on the exchange rates for USD/CAD to AUD on the 31 Mar 2020 instead. Please note that I have multiple more exchange rates and dates, but this USD/CAD are just for reference for now.

Table 1:

Extract Date	Customer	Currency	Value (Non AUD)	Value (AUD)	Exchange Rate
31 Jan 2020	A	USD	1000000	1520000	1.52
31 Jan 2020	B	USD	20000	...	1.52
31 Jan 2020	C	CAD	500000	...	1.1
29 Feb 2020	A	USD	1100000	...	1.74
29 Feb 2020	B	USD	25000	...	1.74
29 Feb 2020	C	CAD	520000	...	1.1

Table 2: Currency Exchange Table

Trading Date	Currency	Exchange Rate AUD
31 Jan 2020	USD	1.52
29 Feb 2020	USD	1.74
31 Mar 2020	USD	1.52
31 Jan 2020	CAD	1.1
29 Feb 2020	CAD	1.1
31 Mar 2020	CAD	1.1

Any help much appreciated 🙂

[Greg_Deckler]

@Anonymous - I cover exchange rates in Chapter 5, Recipe 8 of DAX Cookbook. If you just want to see the DAX of how I did it, you can go here to download the PBIX for the chapter. https://github.com/gdeckler/DAXCookbook

[Anonymous]

Hi @Greg_Deckler , I don't think that Receipe really helps, as I don't want a solution where the Currency is able to be selected by the user. It should automatically be calculated.
I only want the the user to change the date of the currency rate - assume that each of the available currency rate is populated for all type of currency codes.

[Greg_Deckler]

@Anonymous - The selecting the right date is part of the recipe and the basics would be the same. So basically, you would use SELECTEDVALUE to grab the date from the slicer (I would recommend a disconnected calendar table for this). Then you would use this to LOOKUPVALUE your exchange rate for that day. Then you could do your calculation (either SUM up something and multiply by exchange rate or whatever, right?

[Anonymous]

Hi @Greg_Deckler , could you explain a little more?

Apologies - but I'm still early days on my learning with DAX. I understand the SELECTEDVALUE, but I'm a little confused as to applying the LOOKUPVALUE.

If I want to use LOOKUPVALUE, how do I make it apply all exchange rates i.e. USD/AUD, CAD/AUD, GBP/AUD within the selected day?

I've just uploaded a below screenshot which may make it easier to explain.

Essentially, I want a user to be able to view the data based on the extract_date. Within this data, there are customers from different countries with different currency type, which I want to convert back to my home currency (AUD).

I want the user to be able to select a trading date (this being the SELECTEDVALUE), which will then apply the exchange_rate for that selected date to the [Value Non AUD] for ALL Currency_Codes.

[Greg_Deckler · Accepted solution]

@Anonymous - I'm still not clear on what you are trying to achieve. Let's say we have a table of dates and transactions kind of like you have shown. You also have a table of daily exchange rates kind of like what you have shown. User picks a date from a disconncted calendar table. Now you want to figure out some calculation. Let's say it is that you want to take all the transactions, apply the exchange rate to the transaction as of the day that has been selected and then calculate the sum of these. In this specific case:

Measure =
  VAR __Date = SELECTEDVALUE('Calendar'[Date])
  VAR __Table = 
    ADDCOLUMNS(
      'Transactions',
      "EffRate",
      LOOKUPVALUE('ExchangeRates'[Value],'ExchangeRates'[Date],__Date,'ExchangeRates'[Type],[Type])
    )
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "Product",
      [EffRate] * [PurchaseAmount]
    )
RETURN
  SUMX(__Table1,[Product])

I'm making up table names and column names because you didn't post anything in text below and I can't read the tiny picture while also trying to code. But, in short, grab your date. Add a column to your base fact table that is essentially the exchange rate for the date selected as well as the current row's "type" USD/CAN, CAN/USD, etc. Add another column to that table that multiplies some value by this rate. Sum everything together, the end.

[Anonymous]

Hi @Greg_Deckler , first-of, thanks for your help so far... even if I'm not getting there yet

I'm adding some pictures to better illustrate my point as to what I'm trying to achieve:

When I use the measure you created, I only get blank results when I drag it onto the table for display:

And for fuuther reference:

Sample PBIX can also be found here: 

https://1drv.ms/u/s!ArqhX9Cz43yboxmYSEX1ngG57274?e=EdAIcp

[Anonymous]

Hi @Greg_Deckler,

I seemed to have solved it. I had to just do some minor tweaking to your code.

With regards to the steps I took - I did some troubleshooting with experimenting with your DAX, and realised that the Blank Values were in part to do with the first VAR Table I created. 

Instead of making the Date a variable, I shifted it to the LOOKUPVALUE DAX itself. That seemed to have worked. Unsure as to what the behaviour is behind it working as a fixed DAX, versus a variable (i.e. VAR ___DATE = SELECTEDVALUE(Calendar[Date])

Value based on selected Currency Date = 
VAR ___TABLE = 
ADDCOLUMNS(
    'ValueTable',
    "EffRate",
    LOOKUPVALUE('CurrencyTable'[AUD_rate],CurrencyTable[Currency Date],SELECTEDVALUE(CurrencyTable[Currency Date]),CurrencyTable[Currency Code],'ValueTable'[Currency_Code])
)
VAR ___TABLE2 = 
    ADDCOLUMNS(
___TABLE,"Product",
[EffRate] * [Final Value (Non AUD)])
RETURN
SUMX(___TABLE2,[Product])

[Greg_Deckler]

@Anonymous  - Awesome!