SSAS期初余额
我正在 SSAS 中构建一个多维数据集，它使用库存移动数据来显示项目活动。可以在此处查看此数据的示例（希望这是可读的）。
日期 = 客户 = 项目 = 接收数量 = 发货数量 = 移动
1/1/2000 = Cust123 = 项目123 = 100 = 0 = 100
11/5/2011 = Cust123 = 项目123 = 10 = 0 = 10
11/6/2011 = Cust123 = 项目123 = 0 = -5 = -5
11/7/2011 = Cust123 = Item123 = 1 = 0 = 1
目标是在多维数据集中包含期初余额。给定日期的期初余额定义为给定客户和项目（我的事实表包含许多不同的客户和项目）在当前日期之前的所有日期的所有变动（来自“变动”字段）的总和。
因此，在示例数据中，2011 年 11 月 6 日日期的期初余额将是客户和项目匹配且日期小于 2011 年 11 月 6 日的所有移动（来自“移动”字段）的总和。在我的样本数据中，这可以追溯到 2000 年 1 月 1 日。
MDX 有什么方法可以帮助我在我的多维数据集中完成此任务吗？我对 SSAS 比较陌生，但我相信应该有一种方法可以在“计算”选项卡中包含一些 MDX，对吗？任何帮助将不胜感激。谢谢。
I am building a cube in SSAS that uses inventory movement data to show item activity. A sample of this data can be seen here (hopefully this is readable).
Date = Customer = Item = Receive Qty = Ship Qty = Movement
1/1/2000 = Cust123 = Item123 = 100 = 0 = 100
11/5/2011 = Cust123 = Item123 = 10 = 0 = 10
11/6/2011 = Cust123 = Item123 = 0 = -5 = -5
11/7/2011 = Cust123 = Item123 = 1 = 0 = 1
The goal is to include an opening balance in the cube. The opening balance for a given date is defined as the sum of all Movement (from the Movement field) for a given customer and item (my fact table contains many different customers and items) for all dates prior to the current date.
So in the sample data, the opening balance for the 11/6/2011 date would be the sum of all Movement (from the Movement field) where the customer and item are a match, and the date is less than 11/6/2011. Which would go all the way back to 1/1/2000 in my sample data.
Is there a way that MDX could help me accomplish this in my cube? I am relatively new to SSAS, but I believe there should be a way for me to include some MDX in the Calculations tab correct? Any help would be greatly appreciated. Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一种快速的肮脏方法是：
这假设了一些事情：
Date Hierarchy的CurrentMember将处于日级别。如果您有更大的数据集并且这对您来说很慢，那么您需要做的就是首先总结前几个月（或前几年，取决于您的分区级别），然后再计算本月的天数它。
A quick dirty way would be:
This assumes a few things:
CurrentMemberof theDate Hierarchywill be at a day level.If you have a much larger dataset and this is slow for you, what you'll want to do is to sum up the previous months (or years, depending on your partition level) first, and then do days of this month on top of it.
我会考虑捕获基础表中的运行值。对现有的所有内容进行计算不应该太多，并且新数据将是轻而易举的事情。
然后，您可以使用 LastNonEmpty 度量（如果您有企业版），它应该可以即时响应任何地方的任何余额。
I would look at capturing the running value in the underlying table. It shouldn't be too much to calculate for everything existing, and new data would be a breeze.
You could then use LastNonEmpty measure (in case you have Enterprise edition) and it should be instant-response for any balance anywhere.