MDX filter by date range or condition on measures

Question

I have an MDX query which gets me how many Completed/Booked/Unbooked Maintenance Jobs there are for each Vehicle

SELECT NON EMPTY { [Measures].[Completed], 
[Measures].[Unbooked],
[Measures].[Booked]} ON COLUMNS, 

NON EMPTY { (
[Job Code].[Code].[Code].ALLMEMBERS*
[Vehicle].[Vehicle Number].[Vehicle Number].ALLMEMBERS*
[Job Group Target Completion Date].[Date].[Date].ALLMEMBERS
) } ON ROWS 

FROM ( SELECT ( STRTOMEMBER(@FromDate, CONSTRAINED) : STRTOMEMBER(@ToDate, CONSTRAINED) )
FROM [Standard Reports - Depot Work Analysis])

Where @FromDate and @ToDate are members from my [Job Group Target Completion Date] dimension. Jobs are grouped into "Job Groups" when they share Job Code and Target Date.

How can I expand this so that I either get all Job Groups between the target dates (as it is now) OR all Job Groups in which not all jobs are completed (i.e. [Measures].[Unbooked] > 0 or [Measures].[Booked] > 0) but were targeted to be completed before the given date range.

Answer 1

I expect you want something like the following:

With 
Member [Measures].[Targeted] as 
IIF(
    [Measures].[Unbooked] > 0
    and
    [Measures].[Booked] > 0
    and 
    [Measures].[Completed] > 0,
    1,
    Null
) 


Select 
Non Empty {
    [Measures].[Completed],
    [Measures].[Unbooked],
    [Measures].[Booked]
} on 0,
Non Empty {
    {
        [Job Code].[Code].[Code].AllMembers *
        [Vehicle].[Vehicle Number].[Vehicle Number].AllMembers *
        {StrToMember(@FromDate, CONSTRAINED):StrToMember(@ToDate, CONSTRAINED)}
    {
    +
    NonEmpty(
        {
            [Job Code].[Code].[Code].AllMembers *
            [Vehicle].[Vehicle Number].[Vehicle Number].AllMembers *
            {NULL:StrToMember(@FromDate, CONSTRAINED).Lag(1)}
        },
        [Measures].[Targeted]
    )
} on 1 

From [Standard Reports - Depot Work Analysis])