Measure to convert running balance based on Fx rate

[KarlNixon]

Looking for some help with setting up a measure to convert a running balance based on the Fx rate of the day of the balance date.

I have three tables

I have 2 measures

Total Amount

Running Balance

I'm after a measure which multiplies the running balance by the relevant Fx rate 

ie for

April 2021 = 40,000 x 7.55

May 2021 = 50,000 x 7.54

I suspect the answer would be using a TREATAS function but haven't been able to work out the syntax.

Any assistance would be greatly appreciated!

[v-yangliu-msft]

Hi  @KarlNixon ,

1. You can create a relationship between the table DimCalendar and the table fx with the column [date].

2. Create measure.

Running Balance USD =
[Running Balance] * MAX('fx'[fx])

3. Result:

If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@KarlNixon , if FX table has unique dates only, you can join on dates and use related to get the fx

sumx(balances,related(fx[fx]) *[Amount] )

Treatas another option

You can copy value to balances

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[KarlNixon]

Thanks amitchandak,

But that doesn't seem to work