Lookupvalue help

[Anonymous]

Hi,

Would appreciate help with mapping a DAX formula.

I have a lookupvalue that checks the currency for each day, here is the formula:

ExchangeRateUSD = IF(ISBLANK(LOOKUPVALUE(
    'All currencyExchangeRates'[relationalExchangeRateAmount],
    'All currencyExchangeRates'[StartingDateFormatted], Opportunities[ExchangeRateDate],
    'All currencyExchangeRates'[currencyCode], Opportunities[CurrencyFormat_USD])), 
    LOOKUPVALUE(
    'All currencyExchangeRates'[relationalExchangeRateAmount],
    'All currencyExchangeRates'[StartingDateFormatted], DATE(Opportunities[ExchangeRateDate].[Year], Opportunities[ExchangeRateDate].[MonthNo], Opportunities[ExchangeRateDate].[Day]) - 3,
    'All currencyExchangeRates'[currencyCode], Opportunities[CurrencyFormat_USD]), 
    LOOKUPVALUE(
    'All currencyExchangeRates'[relationalExchangeRateAmount],
    'All currencyExchangeRates'[StartingDateFormatted], Opportunities[ExchangeRateDate],
    'All currencyExchangeRates'[currencyCode], Opportunities[CurrencyFormat_USD]))

Here is a summary of what it does:

• It checks if the exchange rate for the given currency code and exchange rate date in the Opportunities table is blank.
• If the exchange rate is blank, it looks up the exchange rate for the same currency code and a date that is 3 days prior to the exchange rate date in currencyexchangerates table.
• If the exchange rate is still blank after the second lookup, it falls back to the original exchange rate for the given currency code and exchange rate date in the currencyExchangeRates table.

Is there any way to fix to make a lookup so that it looks a value within 3 days prior, and if there is data perhaps one day prior instead, it chooses that day instead of 3? An example would be:

If we have data missing for example on the 15th of January, and data on the 14th January and 12th of January, I would like it to grab data from the 14th instead?

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can use the function with a function that searches for the closest date within a range of dates.

Below is the DAX might work for you:

ExchangeRateUSD = IF(ISBLANK(LOOKUPVALUE(
    'All currencyExchangeRates'[relationalExchangeRateAmount],
    'All currencyExchangeRates'[StartingDateFormatted], Opportunities[ExchangeRateDate],
    'All currencyExchangeRates'[currencyCode], Opportunities[CurrencyFormat_USD])), 
    LOOKUPVALUE(
    'All currencyExchangeRates'[relationalExchangeRateAmount],
    'All currencyExchangeRates'[StartingDateFormatted], 
    MAXX(
        FILTER(
            'All currencyExchangeRates',
            'All currencyExchangeRates'[currencyCode] = Opportunities[CurrencyFormat_USD] &&
            'All currencyExchangeRates'[StartingDateFormatted] >= DATE(Opportunities[ExchangeRateDate].[Year], Opportunities[ExchangeRateDate].[MonthNo], Opportunities[ExchangeRateDate].[Day]) - 3 &&
            'All currencyExchangeRates'[StartingDateFormatted] <= Opportunities[ExchangeRateDate]
        ),
        'All currencyExchangeRates'[StartingDateFormatted]
    ),
    'All currencyExchangeRates'[currencyCode], Opportunities[CurrencyFormat_USD]), 
    LOOKUPVALUE(
    'All currencyExchangeRates'[relationalExchangeRateAmount],
    'All currencyExchangeRates'[StartingDateFormatted], Opportunities[ExchangeRateDate],
    'All currencyExchangeRates'[currencyCode], Opportunities[CurrencyFormat_USD]))

This modified formula uses the function to find the closest date within a range of dates. The function searches for all dates within 3 days prior to the exchange rate date and the exchange rate date itself.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.