sumx with date range lookup

[django]

Hi I have an issue with my  report. I cannot share my actual data for privacy so I have created a mockup model to explain my issue
I have spent a lot of time on it but it is behond my skills and i cannot figure out a solution.


I have two table

one fact Table

And one Dimension Table

Here is my model connections

I have a many to many relationship between the two tables

Now what I want is to define a measure to be displayed in a matrice

This is the matrice and i want to add a new value column wich would be
The sum of orders * the Time expense
But... the time expense varies

I have a from date and a to date column in my dimension table

I need the correct value to match the date of order.

I have seen plenty of exemples where a calculated colum is created.
However my data set is much more complex than my mockup and I would need lots of calculated colums.

I need a measure definition.

Any help would be greatly appraciated..
I am a bit worried about this Many to many relationship, maybee my model is wrong all togeather

[django]

I forgot to link the pbx file

Here it is

Link to mockup pbx file