Stock Turn : Inventory TurnOver

Question

I am trying to Calculate Stock Turn = (COGS for last 12 months from Current date)/Average Inventory Cost for last 12 months).So my 1st step is to Calculate COGS for last 12 months(Cost of Good Sold). I am using the following Query:

SUM( ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12):ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]), [Measures].[Cogs Amount])

But the Calculated Member is giving me Null results.

Please help.!!

Cheers Rushir

Answer 1

You are using [ALL Periods]. You should not use that member because it is the root. The Lag(12) of [All Periods] doesn't exist.

Try using an specific member, something like this:

SUM({ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[2011].[11]).Lag(12):ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[2011].[11])}, [Measures].[Cogs Amount])

Or something like this:

SUM({[Date].[Calendar].CurrentMember.Lag(12):[Date].[Calendar].CurrentMember}, [Measures].[Cogs Amount])