Calculate average future price by contract in a many to many relationship

[Aleph18]

Hi,

I need help please to calculate a column of average future price by contract  (many to many relationship) between two dates. 

[Ashish_Mathur · Accepted solution]

Hi,

Remove the relationship between the 2 tables.  Write this calculated column formula in CAKstartEndDates table

=calculate(average(Benchmark[value]),filter(benchmark,benchmark[value]>=earlier(CAKstartEndDates[Start date])&&benchmark[value]<=earlier(CAKstartEndDates[End date])&&benchmark[FUT]=earlier(CAKstartEndDates[Future])))

Hope this helps.

[Nathaniel_C]

Hi @Aleph18 , It is not a best practice to connect two tables with a many to many relationship.  Instead create a calendar table which will allow you to connect the two tables by a 1 to * relationship from the calendar table to each table.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel