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.

Could you please be more specific about your KPIs? They are the keys to your math. It's not clear why do you need not to lose other than year levels here. Show the business goal, not an abstract one. — Danylo Korostil
As an example the measure is the number of cases created by a sales adviser. The target to reach is the previous quarter's average for that adviser. So if I'm looking at a single days activity, I want to compare it against the daily average of that previous quarter. If I'm looking at a normal weeks activity I want to see 5 * the daily average to make a comparable target (because we only work Mon - Fri.) When I choose this current month filter as there are 11 more working days left this month these 11 are multiplying the target when there is no activity to compare in the current month. — TJH
So if my date filter can prevent future days from showing in MDX then it won't count days and multiply my targets unnecessarily. — TJH

TJH TJH · Accepted Answer · 2017-10-26T14:09:18

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
)

MDX Date Filter For PerformancePoint Dimension

1 Answers