create a calculated measure in MDX that Filters by

Question

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.

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

Answer 1

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

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.