I have a requirement to calculate measure value between 2 dates.Please find below the requirement.Sales Deal Contract Opportunity No Customer(Merged) Material(Merged) Date of First Invoice(A) End Date(B) Measure 1 From Bex Query 1 From Bex Query 1 From Bex Query 1 From Bex Query 1 From Bex Query 2 From Bex Query 2 Measure restricted on Calday(From Query 1): From A to B
Basically, I have to calculate measure value based on the 2 Dates coming from Bex Query 2.
When I add Calday(from Query 1) to the above table layout, I am able to see the measure value getting calculated correctly. But, When Calday is removed from the Table layout #MULTIVALUE Error is seen.
Note: I have tried using context Operators IN/FORALL./FOREACH(along with SUM function on measure), but its still showing # MULTIVALUE.
Using In Function:
=Sum([Measure]) Where([Query 1].[Calendar day] In([Query1].[Sales Deal Contract];[Query1].[Opportunity No];[Query1].[Customer Name];[Material]) between ([Date of First Invoice];[End Date])
Similarly, I have tried FOREACH/FORALL Functionalities. In all the cases I am getting #MULTIVALUE Error.
Can you please advice.