How to construct date range query when not all dates exist in hierarchy?

Question

New to MDX and inherited an application using the following to retrieve the last 7 work days.

Note, the actual dates are generated dynamically.

WHERE  ( [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-25T00:00:00]
: [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-26T00:00:00].lag(6)
)

I expected it to use the previous 7 work days:

• 2016-03-17 to 2016-03-25

But instead, it uses future dates

• 2016-03-25 to latest date in DimCalendar

From what I've read, it's because 2016-03-26 doesn't exist in the hierarchy, so the end range becomes NULL, which explains the future dates...

[WorkDate].&[2016-03-25T00:00:00] : NULL

The problem is the date values are generated dynamically, and I don't know in advance which values exist in the hierarchy. I'm not sure how to construct the MDX date range to get the desired results.

I've tried using <= and FILTER but keep getting conversion errors. With plain SQL this would be easy. I could just write:

WHERE [WorkDate] >= '2016-03-17' 
AND   [WorkDate] <= '2016-03-25'

Any ideas what the equivalent filter would be in MDX?

Answer 1

A quick fix could be

WHERE ( [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-25T00:00:00].lag(7) : [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-25T00:00:00] ) But this will only work if the past date is in the hierarchy, which in this case in 2016-03-25.

Edit: Based on the issue below

///Query without using strong names . (no &)

select {[Measures].[Internet Order Count] }
on columns,
[Date].[Day of Year].[1]:[Date].[Day of Year].[10]
on rows 
from [Adventure Works]

//This query filters by making the dimension member value, as a measure value.

WITH 
MEMBER [Measures].[Data Type] AS 
[Date].[Day of Year].CurrentMember.Properties ("Member_Value",TYPED) 
select {[Measures].[Internet Order Count] }
on columns,    
filter ([Date].[Day of Year].[Day of Year],[Measures].[Data Type]<12)
 on rows 
from [Adventure Works]

//You can also try the below one

select 
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]} 
on columns, 
filter([Date].[Day of Year].[Day of Year], 
[Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)
>12 and [Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)<20) 
on rows 
from 
[Adventure Works]

Edit

//this might be the exact solution that would work for you

select 
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]} 
on columns, 
([Geography].[Country].&[United States]
)
on rows 
from 
[Adventure Works]
where 
filter([Date].[Day of Year].[Day of Year], 
[Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)
>12 and [Date].[Day of Year].currentmember.Properties ("Member_Value",TYPED)<20)