在MDX中创建一个按日期范围过滤的计算量度 [英] create a calculated measure in MDX that Filters by Date Range

问题描述

我正在尝试创建一个计算所得的成员来计算员工年初至今的nb.到目前为止，我的意思是在任何给定时间段内的雇员人数.我的事实表有2个日期维度，即StartDate和EndDate.我想按如下方式计算年初至今的雇员.

I am trying to create a calculated member to calculate the nb of employees YTD. By YTD I mean the number of employees for any given period of time.My fact table has 2 date dimensions StartDate and EndDate. I would like to calculate YTD employees as follows.

StartDate等于或早于当前期间的成员 和 当前期间的EndDate或EndDate为NULL

Members with StartDate equal to or before current period AND EndDate in the current period OR EndDate is NULL

推荐答案

我有类似的任务，最终得到以下解决方案:

I had a similar task and end up with the following solution:

SUM(
    [EmployeeChanging].[EmployeeChanging].[EmployeeChanging].Members,
    IIF(
        [Measures].[EmployeeFrom] <= [Measures].[MaxDay]
        and 
        [Measures].[EmployeeTo] >= [Measures].[MinDay],
        [Measures].[EmployeeChangingCount],
        NULL
    )
)

有以下格式的暗淡/事实表:

There is the dim/fact table in the following format:

EmployeeID + StartDate + EndDate

EmployeeID + StartDate + EndDate

使用[Measures].[EmployeeFrom]，[Measures].[EmployeeTo]，[Measures].[EmployeeChangingCount]的新维度创建键为EmployeeID + StartDate和基于同一表的度量值组的新维度EmployeeChanging.最大，最大，计数聚合.另外，您还必须根据日期维度提供[Measures].[MaxDay]和[Measures].[MinDay]度量，并为同一日期字段提供最大和最小聚合.而已. 另外，您可以隐藏EmployeeChanging维度，因为只有MDXing才需要.

Create a new dimension EmployeeChanging where the key is EmployeeID + StartDate and a measure group based on the same table with [Measures].[EmployeeFrom],[Measures].[EmployeeTo],[Measures].[EmployeeChangingCount] measures with max, max, count aggregations. Also you have to provide [Measures].[MaxDay] and [Measures].[MinDay] measures based on your Date dimension with max and min aggregations for the same date field. That's it. Also you may hide your EmployeeChanging dimension as it required only for MDXing.

这篇关于在MDX中创建一个按日期范围过滤的计算量度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！