Adjust date range to existing members in MDX

Question

I have a report with DateFrom and DateTo parameters. Both are calendars - you can select a date with day precision.

One of the datasets in the report is an MDX one. I use strtoset to feed the date range to it, and the expression for the argument to strtoset goes:

="([Test Run].[Complete Date Hierarchy By Month].[Date].&[" & Format(Parameters!FromDate.Value, "yyyy-MM-dd") & "T00:00:00]:[Test Run].[Complete Date Hierarchy By Month].[Date].&[" & Format(Parameters!ToDate.Value, "yyyy-MM-dd") & "T00:00:00])"

The problem is, if the start date and the end date are not members in the dimension, the query returns all nulls.

Any way to adjust the user provided date to the closest existing member in the dimension, either forward (for From) or backward (for To)?

EDIT: I'm now trying to generate a set of date values in the [From:To) range with Filter. It works in the sense that it doesn't error out, but the resultset is different from what what I had with strtoset(). There's the following in the Where clause now:

filter([Test Run].[Complete Date Hierarchy By Month].[Date],
    [Test Run].[Complete Date Hierarchy By Month].currentmember.member_value >= CDate('2017-10-01') and
    [Test Run].[Complete Date Hierarchy By Month].currentmember.member_value < CDate('2018-07-01'))

And I'm getting slightly different numbers. How's that even possible?

Seva Alekseyev Seva Alekseyev · Accepted Answer · 2018-08-28T15:52:01

FILTER([Test Run].[Complete Date Hierarchy By Month].[Date],
[Test Run].[Complete Date Hierarchy By Month].[Date].CurrentMember.Member_Value >= CDate('2018-01-01') and
[Test Run].[Complete Date Hierarchy By Month].[Date].CurrentMember.Member_Value < CDate('2018-04-01'))

Adjust date range to existing members in MDX

1 Answers