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

AndreasM — Tue, 09 Jul 2024 08:45:11 GMT

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 🙂

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

some_bih — Thu, 11 Jul 2024 05:39:25 GMT

Hi AndreasM check link with example

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

AndreasM — Thu, 11 Jul 2024 10:39:55 GMT

Hi some_bih, thank you for the reply.

I have tried to follow the guide but I have been unable to make it work. thank you for the suggestion though

topic Re: Dynamic Element in DAX formula within Table View, to update with the report filter in DAX Commands and Tips

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

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

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