Calculate Exchange rate for every day "USD"

[DimaMD]

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

[DimaMD]

@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 

[smpa01 · Accepted solution]

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

[DimaMD]

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

[smpa01]

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

[smpa01]

@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

[DimaMD]

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