Search code examples
sqlssasmdxdata-warehousecube

SSAS Moving Average / Rollup / Cumulative Periods

Let's say we have the current situation in the Cube Browser for Working Days Analysis.

Date8 it's part of the DimDateBST Dimension and it was created in format year-month-day and the WD_Avg is our interested measurement. The logic behind it's simple... If the day in month it's a working day then the value it's 1. If there is no woking day it's 0. If it's a half working day then it's 0,5.

Cube Browser

We need to create a calculated measure to Rollup the measurement WD_AVG based on the type of day. The desired result would be then:

Desired Result

How would be that achievable? Is there any MDX-function that would work here?

Solution

  • The problem was solved by creating the following calculated measure:

     CREATE MEMBER CURRENTCUBE.[Measures].[WD_Cum]
    AS
      SUM({NULL:[Dim Date BST].[Dat Cal].CurrentMember}
         ,  [Measures].[WD_AVG])
    ;

    {NULL:xxx} creates a set of everything before the xyz member, i.e. everything to Dat Cal...

    Result