Calculate Exchange rate for every day

[DimaMD]

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

[smpa01 · Accepted solution]

@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

[DimaMD]

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

[smpa01]

@DimaMD  Can you please create a new thread on this?

[DimaMD]

Hi @smpa01 New post here

[DimaMD]

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

[amitchandak]

@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])