PowerPivot many to many date relationship

[chaserracer83]

I have two tables tbl_Transaction and tbl_SalesCall. Tbl_Transaction contains information on all transactions (Date, charges...). Tbl_SalesCall contains information on all sales calls (Date...). I want to make calculations such as the total charges in tbl_Transaction based on a date in tbl_SalesCall. The date field is not unique in either table. I have a third table tbl_Dates which contains all my contiguous dates.

I have started by creating a relationship between tbl_SalesCall/tbl_Dates and tbl_Transaction/tbl_Dates (I would like to note that PowerPivot puts tbl_Dates in the lookup table column. I have looked over other forums to try and understand what I am doing. and in those forums the many table is always in the lookup table. I am not sure if this is why I am having issues, but I don't know how to address that). Once the relationships are created I test things out by starting with the following calcualted column equation in tbl_SalesCall:

=SUMX(RELATEDTABLE(tbl_Dates),RELATED(tbl_Transaction[Charges]))

I get an error that tells me tbl_Transaction[Charges] doesn't exist or doesn't have a relationship in the current context.

I am a novice to PowerPivot and can't figure out what I am doing wrong. Any guidance would be greatly appreciated.

Thank you,
Chase

This question has also been posted to:
http://www.mrexcel.com/forum/powerpi...ationship.html
and
http://social.msdn.microsoft.com/For...1-3e058071dca9