I need to write a script in my SSAS project (cube calculation) that calculate Opening and closing stock value on specific date.
In this screen short first table showing my raw data and second table is showing my required BI values .
Opening stk (stock) on 2015-02-01
product A was 18 and that day price was 10 so 10*18 = 180
stk value,
on 2015-02-08 price was 9 for same product and closing stk was 10 (so 9*10=90) is a closing stock value.
I have use below code for this but this script is not showing required results.
Scope([Measures].[Closing Stock]);
This = sum((
null:[Time].[Year].CurrentMember *
null:[Time].[Quarter].CurrentMember *
null:[Time].[Month Name].CurrentMember *
null:[Time].[Month].CurrentMember *
null:[Time].[date].CurrentMember )
,[Measures].[Movement Qty]) * Price;
FORMAT_STRING ( This ) = "#,#";
End Scope;
For what I understand you want to display the remaining stock and the remaining stock value for each day. The script below will work only at day levels. I am have assumed that you have the following measurse in your cube [Meausres].[Opening],[Measures].[Qty],[Measures].[Price] and you have [date] dimension and a level named [date] in this dimension
with
member measure.ClosingCount
as
sum([date].[date].currentmember, [Meausres].[Opening]-[Measures].[Qty])
member measure.ClosingAmount
as
sum([date].[date].currentmember, [measure].[ClosingCount]*[Measures].[Price] )
Edit based on the comment
with
member measure.ClosingCount
as
sum(([date].[date].currentmember,[Product].[Product].currentmember), [Meausres].[Opening]-[Measures].[Qty])
member measure.ClosingAmount
as
sum(([date].[date].currentmember,[Product].[Product].currentmember), [measure].[ClosingCount]*[Measures].[Price] )