DAX - Approximate lookup of Dates

[sdcha2]

Apologies if this has been answered previously, I haven't been able to see a similar situation with dates.

I wanted to do an equivalent of an approximate vlookup in DAX but couldnt find out how to do it.

I have two tables, one 'FX Rate' table with Date, Currency and FX rate (to AUD) and

One Fact table, the values of which are in various currencies, but for which I want to create a measure to convert to AUD using the FX rates.

Unfortunately weekends are not included in the FX table whereas the fact table is end of month only data, for the majority of months this is not an issue, but there are a number of months where the EOM falls on the weekend hence it cannot match a rate for that date.

Here is the measure I have created that almost does what I need:

AUD_Value =

SUMX('Monthly',

    'Monthly'[Value]/

        LOOKUPVALUE('FX Rate'[FX Rate],

            'FX Rate'[Date], 'Monthly'[Month of Data],

            'FX Rate'[Currency], 'Monthly'[Currency]))

Thanks for your assistance!

[CoreyP · Accepted solution]

I'm really not sure if this is the best way to do this, but it's where my thought process took me, and it does work. 

I added a new column to the FX Rate table:

Then I created a calculated table to expand FX Rates so that there is a row for every date:

Now you have an FX Rate table that has a rate for every date.

Let me know if you end up using this method. Cheers!

[sdcha2]

Thanks so much mate, it worked perfectly! 

Such a shame they don't have a vlookup formula that would capture an approximate match though, could be a lot easier!

[CoreyP]

Glad you chose to use it! Meh, I hear you, but to be perfectly honest if a fuzzy lookup existed, it still wouldn't necessarily be the best way to build your data model. There is a way we could write a DAX measure that would accomplish what you're after -- but I maintain my original method is likely the better of the options.