#MULTIVALUE Issue - Formula help on a merged cell (ForEach?)


Any help would be greatly received as this issue is driving me mad!!

I have 2 queries, one showing a list of customers that have ‘Opportunities’ and another showing ‘Sales’. I have merged on customer number so that I can only show sales for those particular customers that have an opportunity.

The customer’s with opportunities have a Revenue Start Date and I need to calculate the sales for each customer but only from after this particular date. I have managed to get this bit to work in a Cross table showing billing date along the top and customer number down the left using the following formula:

Net Value Since Revenue Start Date =If([Billing doc. date]>=[First Date];Sum([Net Value Bef Chgs]);0)

Any date that is prior to it’s ‘Revenue Start Date’ (First Date) is 0 and any after have the correct value in it.

Now the problem is when I want to show this Net Value Since Revenue Start Date by month rather than by the billing date.

I have tried numerous formulas, one being this:

=Sum([Net Value Since Revenue Start Date]) ForEach ([Billing Date (MMM-YY)])

but they all end in #MULTIVALUE and I’m not sure where I am going wrong. It feels like it should be an easy thing to calculate.

Try this

=Sum([Net Value Since Revenue Start Date]) ForEach ([Billing Date (MMM-YY)]) in ([Customer])