Stock Turn : Inventory TurnOver

0
votes

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

1
ssasmdx
My Select statement is with member measures.Cogs_last_12 as sum( closingperiod([Date].[Date].[Cal Month],[Date].[Date].[All]).lag(2) : ClosingPeriod([Date].[Date].[Cal Month],[Date].[Date].[All]), [Measures].[Cogs Amount]) select measures.Cogs_last_12 on 0 from Inventory – ace_mccloud

1 Answers

2
votes

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])