Pirvu Georgian Pirvu Georgian - 1 year ago 63
SQL Question

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?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download