Hi IT Support,

Your logic is that from the given year, you want all dates before that for the start date and all dates after that for the end date, yet your example screenshot shows the last date for start date to include 2021-01-01 when the year is 2021.

IT Support said:
input ship date (Year) = YYYY
Then
Contract start date list of all values before 01-01-YYYY
Contract end date list of all values after 01-01-YYYY

Based on your defined logic, should not the last start  date be 2020-12-31 when 2021 is selected? And the first end date should be 2021-01-02?

Can you clarify this?

Thanks,

Ian

Thanks for the clarification, very helpful.

OK, this is an interesting problem, as you would like a single filter on year to apply to two sets of Ranges, but those Ranges are on different hierarchies, namely Start Date and End Date. We cannot therefore simply take a single hierarchy like Start Date Year as the filter, as it would not interact correctly with the End Date hierarchy, we'd need the End Date Year to do that. So the first problem is how to provide a single Filter which will input into both End Date Year and Start Date Year. Then we'll need to construct two Range sets, one for Start Date and one for End Date. To do that, we'll need to identify the start and end dates of the ranges, based on the year value supplied.

Create a Global Parameter that will consist of a set of values in a Year type format. A Global Parameter is not tied to any one hierarchy and is simply a list of arbitrary values:

Create a dynamic list that is defined by the start and end dates for Start Date. To do that, we'll need to construct the Start Date Year from the Global Parameter an use that to find the Opening Period of Start Date based on the Start Date Year, which if the year is 2023, will be 2023-01-01, i.e. the end point of the range for Start Date.

So first is to create the Start Date Year from the Global Parameter. We can use the function StrToMember() to map the Global Parameter to the start Date Year. We'll have to drop down into Script mode for a custom list to achieve this:

StrToMember([data].[Start Date year], [global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11])

Now we need to identify the opening period (I.e. first date) that exists for the Start Date Year 2023:

OpeningPeriod([data].[Start Date],StrToMember([data].[Start Date year], [global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11]))

This will be the end point of the Start Dates Range.

We need to find the start point for the range which will be the first element of the Start Dates Hierarchy which we can do using the FirstEelement() Function:

FirstElement([data].[Start Date])

Now we need to put then together as the start and end point of our Range function to produce the set of Start Dates, from inception of the model, to the first day of the selected year:

Range(FirstElement([data].[Start Date]),OpeningPeriod([data].[Start Date],StrToMember([data].[Start Date year],[global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11])))

So now we have our Start Date set of dates.

The construction of the End Dates set of dates follows a similar approach, but this time we need to use the Global Year parameter to construct the End Date Member that will be used to find the starting point for the range of End Dates. Here's the completed expression, I'll leave it for you to decompose the elements.

Range(OpeningPeriod([data].[End Date],StrToMember([data].[End Date year],[global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11])),LastElement([data].[End date]))

Now we have the sets of Dates we need, driven by  a single global parameter. To apply them to any Discover View, select the Range in each hierarchy and drag it to the Filter drop zone. Select "Combined Elements" to show content where the Start or end date is included in any of the values  in the respective ranges:

We now have our final analysis. Selecting global Year 2020, will show all Transactions which have a Start Date on or before 2020-01-01, and an End Date of on or after 2020-01-01:

Hope that helps!

Ian