I need help on a challenge I am faced with. I have attached the model to put things into context. I have looked at similar topics but can’t seem to find a solution (probably missed something).
The logic here is: I make an FX call on a particular date, say 01/12/2018 (TradeCall table - -fact table). Using this opening date (TradeCall), I want to track the performance of this call via its daily return pulled from the internet (Sample FX Pair table - fact table). We could have multiple calls open at the same time at different dates!
My challenge / headache:
Do a ytd (or cumulative) calculation that shows open & closed calls from the TradeCall (View ID) and their respective daily return from the ‘Sample FX Pair’ from the date the call was open. In effect, filtering the daily return for a particular pair (identified by view Id) in the sample fx pair for only the period between opening date to date or closing date.
Any one with an insight? I went as far as how I think the model should look like. Or maybe I am overcomplicating it?
Thanks for your help