Date tables:
if you create a separate date table, so there are no missing dates, you can then reference this as your one date to rule them all.
that's a bit cryptic but i think it's the way to go.
Have a single date table acting as your calendar, then do some sort of lookup like you did in sheet excel rather than power excel.
Yes, I've got the date table in to link them. But the trouble is when I have a sale on 1/07/18 but no FX rate on 1/07/18 (it was a Sunday) then it returns a blank as there is no corresponding record.
Date tables:
if you create a separate date table, so there are no missing dates, you can then reference this as your one date to rule them all.
that's a bit cryptic but i think it's the way to go.
Have a single date table acting as your calendar, then do some sort of lookup like you did in sheet excel rather than power excel.