Monday, March 19, 2012

Last Month-to-Last-Child

I am trying to write a calculation to give a Last Month reference, I use the following MDX and it works fine for the Day level

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month], 1,
[Time].[Date with Month].CurrentMember
)

However for Month level, I do not want it to reference to the last month value directly. What I want is, for example, today is "7th May 2007", I want the Last Month Reference for "May 2007 " to be the sum of "1st Apr 2007" to "7th Apr 2007" instead of the sum of the whole "Apr 2007".

Does anyone have any idea on how to archive this? Thanks in advance!

Assuming that all days in a month till the last day with data should be included, something like:

Aggregate(

CrossJoin({[Time].[Month Comparison].DefaultMember},
PeriodsToDate([Time].[Date with Month].CurrentMember.Level,

ParallelPeriod([Time].[Date with Month].[Month], 1,
Tail(NonEmpty(Descendants([Time].[Date with Month].CurrentMember,
[Time].[Date with Month].[Day])).Item(0).Item(0))))))

No comments:

Post a Comment