DAX currency translation

[Glen_]

Hi,

I would be really greatful is someone could assist.  I'm very new to power BI and after searching a lot I'm still struggling for a solution here that suits my setup.

My data is all in one currency & I want to convert it into different versions of dollar rates.  The DAX query i pulled together worked okish when I had the same rate for each period (I dont have dates in the data, just numbered financial periods 1 to 12).

However some of my currency scenarios have a different rate per period and they pull in blank, so I need help changing this query to take into account the rates at different periods.

The second issue is in my value column I'm converting there is also volume that I do no want to convert and keep at the same value, this pulls from my P&L column in the data sheet

Current DAX formula:

FX adjusted value =

var value_ = CALCULATE(DATASHEETS[Sum of Value])

var ExchangeRate_ = selectedvalue('Currency'[Currency rate])

return

value_ * ExchangeRate_

Data sheet:

Currency sheet:

Also including relationships if this helps - the Period reference is what links them

Thanks for any assistance

[Glen_]

doesn't like valueis, in the formula - any ideas?

[Anonymous]

Oh my bad valueis is the measure of sum of values

Correct amount = IF(FIRSTNONBLANK(Daatasheet[P&L],1)<>"Volume",SUMX('Currency','Currency'[Currency rate] *[sum of values]),SUM(Daatasheet[Value]))

[Anonymous]

can you please show me the table before addin the measure?