topic Calculate Exchange rate for every day "USD" in DAX Commands and Tips

Calculate Exchange rate for every day "USD"

DimaMD — Thu, 25 Nov 2021 18:12:50 GMT

Hello community!

In previous post we had an issue that we needed to fill empty cell with exhange rate from cells of the closest calendar data.

@smpa01 helped us to solve this task here

But now I need to make some more calculations, and for that purpose we need to make a collum which will display all cells in USD. We need to multiply mesure M2 with USD currency, so we made a collum with USD currency, but the problem is when we have a row in table, that displays EUR, PLN etc, the cell stays empty. We need to fill all empty cells, no matter what currency was in a row, with USD

Files example

Re: Calculate Exchange rate for every day "USD"

smpa01 — Thu, 25 Nov 2021 18:26:35 GMT

DimaMD to confirm, what I provided you previously, it should only do the calculation for "USD" only, meaning if the cell value is USD then do the calculation accordingly otherwise overwrite any other currecny as "USD" and do the calculation? Please confirm

Re: Calculate Exchange rate for every day "USD"

DimaMD — Thu, 25 Nov 2021 18:34:01 GMT

Yes, correct. If the row have other currency, we need to overwrite it as closest “up to date” "USD" and do the calculation.

Re: Calculate Exchange rate for every day "USD"

DimaMD — Thu, 25 Nov 2021 19:56:31 GMT

smpa01
I managed to find a solution on my own
I unscrewed the table of exchange rates "USD"

then created a column in which the exchange rate for the previous day is returned

and so I got the desired result

I hope it will be useful for someone, what is your opinion smpa01

Re: Calculate Exchange rate for every day "USD"

smpa01 — Thu, 25 Nov 2021 20:16:06 GMT

DimaMD I did not have time at all to look into it; will update you once I get a chance.

Re: Calculate Exchange rate for every day "USD"

smpa01 — Fri, 26 Nov 2021 16:04:05 GMT

DimaMD try out this measure along the same line as the previous one

Measure4 = VAR _0 = ADDCOLUMNS ( Costs, "newCurrency", "USD" ) VAR _2 = ADDCOLUMNS ( _0, "dt2", VAR dt = CALCULATE ( CALCULATE ( MAX ( 'Exchange rate'[Date] ), TREATAS ( SUMMARIZE ( _0, [Date], [newCurrency] ), 'Exchange rate'[Date], 'Exchange rate'[Currency] ) ) ) RETURN IF ( dt = BLANK (), MAXX ( FILTER ( 'Exchange rate', 'Exchange rate'[Currency] = EARLIER ( [newCurrency] ) && 'Exchange rate'[Date] <= EARLIER ( [Date] ) ), 'Exchange rate'[Date] ), dt ) ) VAR _3 = ADDCOLUMNS ( _2, "val", MAXX ( FILTER ( 'Exchange rate', 'Exchange rate'[Currency] = EARLIER ( [newCurrency] ) && 'Exchange rate'[Date] = EARLIER ( [dt2] ) ), 'Exchange rate'[Exchange rate] ) ) RETURN SUMX ( _3, [val] * [Costs] )

Re: Calculate Exchange rate for every day "USD"

DimaMD — Sat, 27 Nov 2021 20:48:19 GMT

Hi, smpa01

so this is also a great solution, I prefer to describe measure. my experience in dax is still small there is still something to learn