Last exchange rate

[krzysztof]

I have a problem with dax in Power Query:
Where null is empty, the new column finds the last course after the date assigned to the corresponding column
The expected effect is the "target column"

PBIX: https://drive.google.com/file/d/1Ywet6f7_poEBmynvGySCMHplnNo3lifV/view?usp=sharing 

[Anonymous · Accepted solution]

Hi @krzysztof ,

You can create a calculated column as below to get it, please find the details in the attachment.

Target Column = 
VAR _date = 'Tabela'[Date]
VAR _currency = 'Tabela'[Currency]
VAR _predate =
    CALCULATE (
        MAX ( 'Tabela'[Date] ),
        FILTER (
            'Tabela',
            'Tabela'[Date] < _date
                && 'Tabela'[Currency] = _currency
                && 'Tabela'[Exchange rate] <> BLANK ()
        )
    )
VAR _preerate =
    CALCULATE (
        MAX ( 'Tabela'[Exchange rate] ),
        FILTER ( 'Tabela', 'Tabela'[Date] = _predate && 'Tabela'[Currency] = _currency )
    )
RETURN
    IF ( ISBLANK ( 'Tabela'[Exchange rate] ), _preerate, 'Tabela'[Exchange rate] )

Best Regards