need MDX help on calculated weighted average based on time

I am working on a project using financial data (chart of accounts) and I need to create a calculated member(measure).

The measure is the dollar amount for the balance sheet accounts as they roll up over the time dimension, they need to be weighted by the number of days in the period. So instead of it being a normal average it should be weighted by the days in the period.

I have Number of days in Month, Quarter, and Year as attributes in the Time Dimension([DaysInMonth], [DaysInQuarter], [DaysInYear])

Here is an example:

Qtr 1 = (Jan. balance x 31)+(Feb bal x 28)+(mar bal x 31)/total number of days in the period (90 in this example)

My Measure Name is Amount and My time Dimension/Heirarchy is [DimTime].[Calendar Time] respectively.

Has anyone created a similar measure? Can you show me the MDX? This is a tight timeline and any help would be GREATLY appreciated!

DRR

While I'm certain you can pull this query off with MDX, I wonder if you might be better served by storing the component values as measures. Think about it this way, no matter where you are in the cube, you will always have to pull balance data from the lowest level, multiply it by a number specific to that month, and then roll up both the number of days in the months affected and the weighted balance before then doing a division operation. I think you might run into performance problems.

So instead, I'd suggest creating two measures. One is DaysInReportingPeriod. The other is WeightedBalance which is your balance times days. Create these in your relational data warehouse or in the DSV. Then, add the measures to the cube with aggregation set to SUM and hide them (Set Visible=False). Then, all you have to do is create a calculation that divides [Measures].[WeightedBalance] by [Measures].[DaysInReportingPeriod].

Good luck,
Bryan