MDX - calculating distinct count on date range

Question

i've got a cube (multidimensional) with

[Person], [Arrival Date] and [Departure Date] dimensions, [Measures].[persons #D] - distinct count of [Person], and fact table containing dates of person's departures and arrivals.

I need to calculate distinct count of persons that "staying here" at user-picked date. that mean i need count Persons that have [Arrival Date] before and [Departure Date] after user-picked date.

User selected date is stored in [Measure].[EndOfPeriodDate].

In Dax/tabular model i can make it easy. something like that:

CALCULATE(COUNTA([Person]); FILTER(FactTable; ('FactTable'[Arrival Date] < [EndOfPeriodDate]) && 'FactTable'[Departure Date] > [EndOfPeriodDate])))

but i really can't understand how write the same in MDX (i'm working with icCube).

I wrote following measure and it works well:

create member [Measures].[PersonsAtEndOfPeriod] AS aggregate ( NULL:[Arrival Date].[Arrival Date].[Day].lookupByName([measures].[EndOfPeriodDate]) ,[Measures].[Persons #D] )

but its only first part. i need to add the second part of my condition

([Departure Date].[Departure Date].[Day].lookupByName([measures].[EndOfPeriodDate]:NULL)

i suppose it might be look like :

NULL:[Arrival Date].[Arrival Date].[Day].lookupByName([measures].[EndOfPeriodDate]) + ([Departure Date].[Departure Date].[Day].lookupByName([measures].[EndOfPeriodDate]:NULL)

but it doesn't work...

QUESTION: how to add second (or more than 2) condition(s) in my measure?

Thanks in advance!

Answer 1

First, the measure [Measures].[#People] should be a distinct count on the person unique id.

The definition should be:

WITH

   MEMBER toto as eval(SubCubeUnion(null:[Departure].[Departure].[Day].[3 Nov 2019],[Arrival].[Arrival].[Day].[8 May 2019]:null ), [Measures].[#People])

SELECT
   toto on 0
FROM [Cube]  

Doc references:

• SubCubeUnion
• Eval

Hope it helps!