SSAS 中 YTD 编写

[weixin_38059951]

在编写MDX时，无法实现YTD,数据均为null， 求指导
测试数据如下
CREATE
TABLE [dbo].[sales](
   
[RowID] [bigint]
IDENTITY(1,1)
NOT NULL,       
--PK
   
[Type] [nvarchar](10)
NULL,
   
[DateID] [int]
NULL,
   
[Amount] [int]
NULL
)
ON [PRIMARY]
 
insert
into sales([Type],[DateID],[Amount])
select
'A','20150126','54'
union all
select
'A','20150209','50'
union all
select
'A','20150413','58'
union all
select
'A','20150504','52'
union all
select
'A','20150615','88'
union all
select
'A','20150713','77'
union all
select
'A','20150824','93'
union all
select
'A','20150928','82'
union all
select
'A','20151012','64'
union all
select
'A','20151214','82'
union all
select
'B','20151228','116'
union all
select
'A','20160118','68'
union all
select
'B','20160215','20'
union all
select
'A','20160222','105'
union all
select
'A','20160307','78'
union all
select
'A','20160418','75'
union all
select
'B','20160516','75'
union all
select
'A','20160523','122'
union all
select
'A','20160718','58'

 
 
CREATE
TABLE [dbo].[DateTime](
   
[FDate] [int]
NULL,     --PK
   
[FYear] [int]
NULL,
   
[FQtr] [int]
NULL,
   
[FMonth] [int]
NULL,
   
[FPeriod] [int]
NULL
)
ON [PRIMARY]
 
insert
into [DateTime]([FDate],[FYear],[FQtr],[FMonth],[FPeriod])
select 20150126,
2015, 1, 1, 201501
union all
select 20150209,
2015, 1, 2, 201502
union all
select 20150413,
2015, 2, 4, 201504
union all
select 20150504,
2015, 2, 5, 201505
union all
select 20150615,
2015, 2, 6, 201506
union all
select 20150713,
2015, 3, 7, 201507
union all
select 20150824,
2015, 3, 8, 201508
union all
select 20150928,
2015, 4, 9, 201509
union all
select 20151012,
2015, 4, 10, 201510
union all
select 20151214,
2015, 4, 12, 201512
union all
select 20151228,
2015, 4, 12, 201512
union all
select 20160118,
2016, 1, 1, 201601
union all
select 20160215,
2016, 1, 2, 201602
union all
select 20160222,
2016, 1, 2, 201602
union all
select 20160307,
2016, 1, 3, 201603
union all
select 20160418,
2016, 2, 4, 201604
union all
select 20160516,
2016, 2, 5, 201605
union all
select 20160523,
2016, 2, 5, 201605
union all
select 20160718,
2016, 3, 7, 201607
/****************************************************************/
创建SSAS Test
 FK如下
DateTime.FDate = Sales.DateID
///////////////////////////////////////////////////////
Cube
目标计算YTD，
结果均为NULL，且出FPeriod
中出现unknown
WITH
MEMBER [Measures].[YTD AggSales] AS
Aggregate(YTD(),[Measures].[Amount]
)
 
SELECT NON EMPTY { [Measures].[Amount],[Measures].[YTD AggSales] } ON COLUMNS,

NON EMPTY { ([DateTime].[F Period].[F Period].ALLMEMBERS ) } 
ON ROWS
FROM [Test]