SQL 到 SSAS 计算脚本

Question

我想将 SQL 代码转换为 SSAA 项目的 SSAS 计算脚本。此脚本的目的是计算特定日期的库存价值。

select  sum(p.movement) Stock
        ,
        ((select top 1  price from ItemMovement_New ch 
                                where ch.ITEMID='10003226' and  ch.Store='F-GJK' and ch.SaleDate <='2019-06-01' 
                                order by SaleDate desc)) Price, 
        sum(p.movement) +  ((select top 1  price from ItemMovement_New ch 
                                                where ch.ITEMID='10003226' and  ch.Store='F-GJK' and ch.SaleDate <='2019-06-01' 
                                                order by SaleDate desc)) [Stock Value]
from ItemMovement_New p
where Store='F-GJK'  and ITEMID='10003226' and SaleDate <='2019-06-01'

结果：-

Stock   Price   Stock_Value
 1      2895       2895

Answer 1

CREATE MEMBER CURRENTCUBE.[Measures].MaxDate AS
0 , VISIBLE = False;
CREATE MEMBER CURRENTCUBE.[Measures].DAYSTODATE AS 
COUNT(NULL:[Time].[Date].CURRENTMEMBER)-1
, VISIBLE = False; 
CREATE MEMBER CURRENTCUBE.[Measures].HADSALE AS 
IIF([Measures].[Price]=0, NULL, [Measures].DAYSTODATE)
, VISIBLE = False; 
SCOPE([Measures].MAXDATE, [Time].[Time].[Date].MEMBERS);  
    THIS = MAX(NULL:[Time].[Date].CURRENTMEMBER, [Measures].HADSALE); 
END SCOPE;
CREATE MEMBER CURRENTCUBE.[Measures].LASTSALE AS
IIF(ISEMPTY([Measures].MAXDATE), NULL, 
(
([Measures].[Price]) ,
[Time].[Time].[Date].MEMBERS.ITEM([Measures].MAXDATE))), VISIBLE = True; 

Answer 2

请注意，

在下面的查询中，价格将不是一个总和度量。它应该是不同的，或者如果您已将其求和度量，则将其除以行数。

with 
member measure.ClosingCount 
as
sum(([date].[date].currentmember,[Product].[Product].currentmember), 
[Meausres].[Opening]-[Measures].[Qty])
measure.ClosingAmount 
as
sum(([date].[date].currentmember,[Product].[Product].currentmember), 
[measure].[ClosingCount]*[Measures].[Price] )
select measure.ClosingAmount on 0,
[Product].[Product].[YourProduct] on 1 
from yourcube 
where 
[date].[date].[2019-06-01]