MDX LastPeriods - Calculated Member?

[Nigel Hellewell]

Hi

Been thrown into the deepend on this one. I've got all the cubes the
business needs except one. I've made a virtual cube, combining the Sales
and InventoryValue cubes into one. All Fine :)

The one I'm having difficulty with Calculation of Inventory Turns as the
Inventory value is not physically stored on a day to day basis, but
calculated "As-Of".

The required formula is Currenty Inventory Value/(CostofGoods Last 3 months)
*4.

I've been playing round in Cube Editor looking at the options, with the
following known "faux pas".

1. The calculation would be a one time, based on current period (month)
2. If using LastPeriods, would have to be (last 4, and subtract current
period ) * 4

Dimensions - Time.
Measures - Inv_Value , COG

I assume this would be best done within the cube and MDX

Here's a rough example of what I hope to achieve. Has anyone come across
this type of quandry before and more importantly, know how to solve it, and
hopefully assist me.

Tia

Squancey

Inv_Val COG COGCalc TurnCalc InvTurn
Jun 0 0
Jul 908 0 0
Aug 854 908 3632
Sep 837 1762 7048
Oct 696 0 2599 10396 14.93678161

[Darren Gosbell]

Post by Nigel Hellewell
The required formula is Currenty Inventory Value/(CostofGoods Last 3 months)
*4.

I would do this by breaking the formula into 3 parts.

1) Get the Current Inventory Value
2) Get the Cost of Goods for the last 3 months
3) Caculate Currenty Inventory Value/(CostofGoods Last 3 months) *4.

-==================================================

WITH

-- Step 1
MEMBER Measures.CurrentInvValue as 'sum(Tail(FILTER(Descendants
(Time.CurrentMember,,LEAVES),(Time.Currentmember,Measures.Inv_Value) >
0),1),Measures.Inv_Value)'

-- Step 2
MEMBER Measures.COG3Mths as 'IIF(Time.CurrentMember.Level IS
Time.Month,SUM(Time.CurrentMember.Lag
(3):Time.CurrentMember,Measures.COG),NULL)'

-- Step 3
MEMBER Measures.Final as 'Measures.CurrentInvValue / Measures.COG3Mths *
4'

SELECT
{Measures.Inv_Value
,Measures.COG
,Measures.CurrentInvValue
,Measures.COG3Mths
,Measures.Final} ON COLUMNS,
Time.Month.members ON ROWS
FROM <Your Cube Name goes here>

-==================================================

Notes:

I have set up the above MDX query so that it should work in the MDX
Sample app. I have not included all the columns in your sample
resultset, I have just done the calc that you said was giving you the
most trouble.

* Step 1 should always give you the last inv_value for the current time
period at any level.

* Step 2 is filtered so that it only returns a value when you are
looking at the month level (you may want to alter this logic)

HTH