MDX Date Filter For PerformancePoint Dimension

Question

Can someone give me some pointer in how to write an MDX query which shows the last X years up til yesterday in MDX? I've searched but by MDX skills are basic at best and I'm struggling to translate examples into the formula box correctly.

My date dimension hierarchy is as follows:

[Date].[Dates].[Year].&[2017]

Whenever I do filter the results I often lose the hierarchy and only show years. I'm hoping that I can find a query which preserves the Year > Quarter > Month > Day structure in the tree structure filter.

Why am I looking to do this? My data is always up until yesterday and when I use the current date range filter on the dashboard for "This Month" it includes all days in the future for October 2017 which makes my KPI's go haywire as there are days which have yet to have any facts occur against them.

Answer 1

I found this MDX snippet which did exactly what I needed it to. Knowing that at least one case is created each day it would then prevent any future dates from showing. The TOPCOUNT shows the last 5000 instances of my date hierarchy (the furthest of which goes back to 2001.) This then fits in to the PerformancePoint custom MDX filter:

ORDER(
NONEMPTY( TOPCOUNT({[Date].[Dates].AllMembers},
               5000,
              [Measures].[Total Cases Created] )), [Date].
[Dates].CurrentMember.MEMBER_KEY, ASC
)