MDX在开始日期和结束日期之间重复测量

Question

考虑以下事实表：

 customer  | StartDate  | EndDate     | FeePerDay | FeeDayDuration
      A    | 01.01.2015 | 03.01.5015  | 10        |  3

是否可以创建一个计算的度量值，该度量值将重复"FeePerDay"直到达到EndDate。如果我创建一个MDX表达式，如（这是伪MDX）：

Select 
   { FeePerDay } on 0,
   {[Customer].[A]} 
   *{ [Date].[31.12.2014] : [Date].[06.01.2015] } on 1,
From Cube

结果应该如下表所示：

 customer | Date         | ValuePerDay
    A     | 31.12.2014   | NULL
    A     | 01.01.2015   | 10
    A     | 02.01.2015   | 10
    A     | 03.01.2015   | 10
    A     | 04.01.2015   | NULL
    A     | 05.01.2015   | NULL
    A     | 06.01.2015   | NULL

Answer 1

未测试，但您可以尝试一下。

WITH MEMBER Measures.ValuePerDay AS
IIF(
    DateDiff(
            "d", 
            EXISTS([Customer].[StartDate].MEMBERS, ([Customer].CurrentMember, FeePerDay)).Item(0), 
            [Date].CurrentMember
            ) <=0
    and
    DateDiff(
            "d", 
            [Date].CurrentMember, 
            EXISTS([Customer].[EndDate].MEMBERS, ([Customer].CurrentMember, FeePerDay)).Item(0)
            ) <=0
    ,
    FeePerDay 
    ,
    NULL
   )
SELECT {[Customer].Members} 
*
{ [Date].[31.12.2014] : [Date].[06.01.2015] } ON 1,
ValuePerDay ON 0
FROM [Cube]