All geek questions in one place

Create a settlement measure in MDX to filter by date

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

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

+1
source share
1 answer

I had a similar task, and in the end I got the following solution:

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

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

EmployeeID + StartDate + EndDate

EmployeeChanging, - EmployeeID + StartDate , , [Measures]. [EmployeeFrom], [Measures]. [EmployeeTo], [Measures]. [EmployeeChangingCount] max, max, count aggregations. , [Measures]. [MaxDay] [Measures]. [MinDay] Date . . , EmployeeChanging, MDXing.

+1

Source: https://habr.com/ru/post/1680334/

More articles:


All Articles