Looking for help to cross filter tables that includes a Date table

DAX DAX Calculations
1

Hi All,

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.

image
image.png817×591 15 KB

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
Bare

2

I think I would probably need an example on this to work through some ideas.

Certainly possible though.

Can you send through a simple sample of the data that replicates the scenario.

Thanks

3

So there’s a little bit to this, but it centers around this technique here called events in progress.

It needs to be solved in the model and with formula

First key thing is this in the model, two inactive relationships from the date to the opening and close dates

image
image946×796 23.5 KB

image

Then a formula technique like this gets you set up properly

FX Calls = 
CALCULATE( COUNTROWS( TradeCall ),
    FILTER( VALUES( TradeCall[opening date] ), TradeCall[opening date] <= MAX( 'Date'[Date] ) ),
    FILTER( VALUES( TradeCall[closing date] ), TradeCall[closing date] >= MIN( 'Date'[Date] ) ) )

image
image744×645 23.7 KB

You see how it now shows a value when the trade was actually open.

Now within the CALCULATE function, you need to setup a measure (instead of the placeholder) I have used.

Want to give it a go from here now that I’ve shown how it works. I’m confident you should be able to work out the measure from this point. Just think deeply about the context you now have in this table and the model as well.

You should probably use variables here to make it easy for yourself.

See how you go on this and come back with any questions.

Thanks