Search code examples
ssasmdx

Calculation Script for SSAS project


I need to write a script in my SSAS project (cube calculation) that calculate Opening and closing stock value on specific date.

enter image description here

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;

Solution

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