Dynamic Element in DAX formula within Table View, to update with the report filter

Hi,

I have only started learning and trying to make PowerBi reports for the last three weeks, so I am a beginner with it.

I have the below question please:

As a company policy we are using the FX for the month the transactions relate to for Profit & Loss transactions. For the Balance Sheet transactions we are using the latest month FX Rates. I have managed to connect the FX rates and write a DAX formula to lookup the FX based on an FX look up code I have (see snips of both below). What I am trying to overcome now is the below:

Let’s say we are in Period 7 but somebody runs the balance sheet up to period 5 within the report using a filter, I want this FX code to be adjusted with the filter to use the FX rates from Month 5. I have tried to create a measure and add that in the DAX formula (to replace the “month(Today())” part) but it’s not working. Do you have any other suggestions please?

FX Code Snip:

FX Code Column:

I upload the FX rates using Excel as then I can use another tool to produce the FX rates (I looked at an API but its too expensive to get approval for the time being). So I create the Lookup FX Code in Excel for the lookup table.

Thank you in advance, any enlightening you can provide it would be really appreciated 🙂