Hi Everyone,

Now I know that this question has been asked on this forum in one form or another, I still cannot find a solution to my problem.

I have a calculated field which is basically a weighted average over two measures. Something like this: C3 = SUM([C1]*[C2])/SUM([C2]). This calculation is absolutely necessary for my purpose.

Now I want this field displayed in the following manner:

1) Quarter-1 number should display the data for March. Quarter-2 number should display the data for June. So basically the last month in each quarter. Quarter-3 number should display the data for August (since we will now be reporting for August)

2) The YTD number should display the data for August (the last reported month)

For this purpose, I made the following calculated fields:

1) Quarterly_Data:

IF (ATTR({FIXED [Level] : MAX(DATEPART('month', [Month]))}) - 3*DATEPART('quarter', ATTR([Month]))) = (ATTR({FIXED [Level] : MAX(DATEPART('month', [Month]))}) - DATEPART('month', ATTR([Month])))

THEN [C3]

END

There are 2 problems here:

2) Yearly_Data:

IF (DATEPART('month', ATTR([Month]))) = ATTR({FIXED [Location] : MAX(DATEPART('month', [Month]))})

THEN [C3]

END

I also have a parameter called 'Timeline' whose entries are: Monthly, Quarterly, YTD.

I have another calculated field which uses Timeline as a factor to display data ata a monthly, quarterly and YTD level.

3) Final Calculated Field:

IF [Timeline] = 'Monthly' THEN [C3]

ELSEIF [Timeline] = 'Quarterly' THEN [Quarterly_Data]

ELSE [Yearly_Data]

END

Now the problem is that this calculation is working when my first calculated field [C3] is not an aggregate. However, as soon as [C3] becomes an aggregate, the calculation stops working.

Can any of the big hitters out there help me out!