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

Calculate Exchange rate for every day

DimaMD — Wed, 24 Nov 2021 15:01:00 GMT

Hello community! I need help in solving following problem

We have a table with currency exchange rate for every day, but in some cases fields with exchange rate in some days are empty.

I use those exch. rates to calculate different currencies in specific days. But when cell of rate is empty we can't make calculation, so in that cases we need to take exchange rate from the closest day where cell have data. How can propery fill those empty cells by taking the closest filled cell of specific currency, where the date of filled cell is closest to empty one? How can I write such calculateble collum?
file exemple

Re: Calculate Exchange rate for every day

amitchandak — Wed, 24 Nov 2021 16:50:55 GMT

DimaMD , the first select date the conversion

example new column

Var _1 = maxx(filter(Rate, Rate[Currency] = Cost[Currency] && Rate[Date]<= Cost[Date]) , Rate[Date])
return
maxx(filter(Rate, Rate[Currency] = Cost[Currency] && Rate[Date] =_1) , Rate[Date])

Re: Calculate Exchange rate for every day

smpa01 — Wed, 24 Nov 2021 17:41:18 GMT

DimaMD can you try out this measure, seems to be working for me

m2 = VAR _1 = ADDCOLUMNS ( Costs, "dt", --grabs the date from Exchange Rate on Currency+Date Partition VAR _dateFromExchangeRate = CALCULATE ( CALCULATE ( MAX ( 'Exchange rate'[Date] ), TREATAS ( SUMMARIZE ( Costs, Costs[Date], Costs[Currency] ), 'Exchange rate'[Date], 'Exchange rate'[Currency] ) ) ) RETURN --if the dateFromExchangeRate is blank, grabs the immediately preceding -- date from Exchange Rate that has valid Exchange Rate ~ LASTNONBLANK 'Exchange rate'[Date] -- done on currency partition -- if the dateFromExchangeRate is not blank returns dateFromExchangeRate IF ( _dateFromExchangeRate = BLANK (), MAXX ( FILTER ( 'Exchange rate', 'Exchange rate'[Currency] = EARLIER ( [Currency] ) && 'Exchange rate'[Date] <= EARLIER ( [Date] ) ), 'Exchange rate'[Date] ), _dateFromExchangeRate ) ) VAR _2 = ADDCOLUMNS ( _1, --looks up the 'Exchange rate'[Exchange rate] based on the date obtained in the previous step "val", MAXX ( FILTER ( 'Exchange rate', 'Exchange rate'[Currency] = EARLIER ( [Currency] ) && 'Exchange rate'[Date] = EARLIER ( [dt] ) ), 'Exchange rate'[Exchange rate] ) ) RETURN SUMX(_2,[val]*[Costs])

The above generates this

if you look at this block

The mesaure grabbed 26.33 from 2021-10-29 and multiplied that

The pbix is attached

Re: Calculate Exchange rate for every day

DimaMD — Wed, 24 Nov 2021 17:51:04 GMT

Hi, smpa01
it's something an incredible, measure works
I am very grateful to you for your help

Re: Calculate Exchange rate for every day

DimaMD — Thu, 25 Nov 2021 08:30:41 GMT

Hi, smpa01
The mesure works correctly and helped me a lot.

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

Re: Calculate Exchange rate for every day

smpa01 — Thu, 25 Nov 2021 14:21:05 GMT

DimaMD Can you please create a new thread on this?

Re: Calculate Exchange rate for every day

DimaMD — Thu, 25 Nov 2021 18:14:48 GMT

Hi smpa01 New post here