August 5, 2010
It’s common in most cubes to have a number of different date dimensions, whether role playing, distinct, or a combination of both. Say for example, Entry Date, Posting Date and Accounting Period. There may also be numerous hierarchies in each date dimension, such as calendar and fiscal calendar, leading to a relatively complicated array of dates to worry about when calculating semi-additive measures.
If we create a date related calculation (i.e. total to date) how do we ensure that this calculation works across all date dimensions?
Lets assume we have a stock movement measure, where each record in the fact table is the change in stock (plus or minus). The current stock level is found by using a calculation totaling every record to date.
CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
SUM({NULL:[Date].[Calendar].CurrentMember}
, [Measures].[Stock Movement]
);
[Note that {NULL:xxx} just creates a set of everything before the xxx member, i.e. everything to date]
This works just fine, if the user selects the [Date].[Calendar] hierarchy. What if the user selects the [Date].[Fiscal] hierarchy, or the [Period] dimension? Basically the calculation wont work, as the MDX expression is only aware of the [Date].[Calendar] hierarchy.
The simple solution is to use the Aggregate function over all of the dimensions that the calculation needs to be aware of:
CREATE MEMBER CURRENTCUBE.[Measures].[Stock Level]
AS
AGGREGATE(
{NULL:[Date].[Fiscal].CurrentMember}
* {NULL:[Date].[Calendar].CurrentMember}
* {NULL:[Period].[Period].CurrentMember}
, [Measures].[Stock Movement]
);
The calculation will then use whichever date or time hierarchy is selected. It will even cope if multiple dimensions are selected, say the calendar on 0 and the periods on 1, both axis will honor the aggregation as expected.
Frog-Blog out.
Posted in Analysis Services | 
Tags: Analysis Services, Calculated Member, MDX, Semi Additive, SSAS, Time Dimension
14 Responses to “MDX Calculated Member Spanning Multiple Date Dimensions”
Leave a Reply
I'm Alex Whittles.
I specialise in designing and implementing SQL Server business intelligence solutions,
and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.
FREE Full Day SQL events!
I like this approach but really need to tweak it a bit. The need is to sum a balance sheet item like Mortgage Amount which is stored in the fact table as an amount per accounting period. I need to sum from beginning of time to the member from the level selected in one hierachy. Something like this:
Calculate;
CREATE MEMBER CURRENTCUBE.[Measures].[UDAGPTDNullGuy]
AS AGGREGATE(PeriodsToDate(
{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Fiscal Year TA],
[Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
*{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Fiscal Quarter TA],
[Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
*{NULL:[Account Period Filters].[Fiscal TA Hierarchy].[Account Period TA],
[Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
, [Measures].[UDAG Number])
),
FORMAT_STRING = “$#,##0.00;($#,##0.00)”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Property Measures’ ;
Any suggestions?
Hi Chris
It looks like you’re overcomplicating this a little – all three attributes that you want to aggregate by (Year, Quarter and Period) are in the same [Fiscal TA Hierarchy], so you don’t need to worry about aggregating across multiple hierarchies. Instead, you can just sum from NULL to the current member of the hierarchy, which will automatically take care of the selected level (year, qtr, period).
Try something like this
CREATE
MEMBER CURRENTCUBE.[Measures].[UDAGPTDNullGuy] AS
Aggregate
(
{NULL : [Account Period Filters].[Fiscal TA Hierarchy].CurrentMember}
,[Measures].[UDAG Number]
)
,FORMAT_STRING = “$#,##0.00;($#,##0.00)”
,VISIBLE = 1
,ASSOCIATED_MEASURE_GROUP = ‘Property Measures’ ;
Let me know if this doesn’t do what you want.
Alex
Excellent – works a treat, I was thinking you’d need a calculation for each hierarchy in the time dimension !
How would one handle this using a DateTool for comparison/aggregation calculations across multiple time dimensions?
SCOPE([DateTool].[Aggregation].[Year To Date]);
THIS =
AGGREGATE(
YTD(
NULL:[Calendar Production].[Prod - Year - Qtr - Mth].CurrentMember *
NULL:[Calendar Accounting].[Acct - Year - Qtr - Mth].CurrentMember)
, [DateTool].[Aggregation].DefaultMember
);
END SCOPE;
Produces a #VALUE! as the YTD function expects a member expression, and the NULL causes it to be considered a tuple.
Thanks!
Hi Nick
If you’re using YTD you wouldn’t need to use {NULL:xxx} as {NULL:xxx} returns a set of history to date. YTD takes a single member and returns you a set of year to date.
You should be able to use
AGGREGATE(
YTD([Calendar Production].[Prod - Year - Qtr - Mth].CurrentMember) *
YTD([Calendar Accounting].[Acct - Year - Qtr - Mth].CurrentMember)
, [DateTool].[Aggregation].DefaultMember)
Hope this helps – let me know if you get any problems
Alex
Alex-
When I use the YTD method you described, I seem to only be able to see values if I have both dimensions present in the query (either row or column). What I’d like to do is have it work even if I’m only looking at one dimension.
I have an Activity Date and an Invoice Date. If someone is looking at just invoice date, it should just work. Instead I get Nulls. Any ideas?
James
Hi James
There should be no need to select members in both dimensions, so long as they both default to the [All] member. If they don’t then the code wont work and you’ll need to manually select a member in both.
Hope this helps
Alex
Alex
I’ve pillaged your code and have got 90% of it to work perfectly, thank you!
I’m using a utility dimension and trying to set up a YTD calc that spans multiple date hierarchies. I can get it to work fine with one date dimension, but as soon as I add a second date dimension the results come up as ‘NULL’ unless I select a member from both date dimensions.
Here’s my code:
AGGREGATE(
YTD([Date1].[Date].CurrentMember) * YTD([Date2].[Date].CurrentMember),
[Measures].[Count])
I’ve also tried with ‘PeriodToDate’ and it does the same thing, also when I run this directly as MDX against the cube.
The base date dimension is set up to default to ‘all’. I have another calculation on the utility dimension that does a simple cumulative calc, using AGGREGATE and {NULL:[Date].[Date].CURRENTMEMBER}, and this works fine across all our date dimensions.
If you have a bright idea I’d be very grateful!
Many thanks
Tim
Hi Tim
The problem that you’ve got is that YTD doesn’t make sense with the All member. YTD and PeriodToDate need a member selected within the year or period. i.e. with YTD(All), which year would it select?
You should find the last descendant of the current member, and then pass that into YTD. Something like this:
YTD(TAIL(DESCENDANTS([Date1].[Date].CurrentMember,,LEAVES),1).item(0))
Then, if All is the current member, the last member will be selected and YTD will have something to go on.
Hope this helps,
Alex
Hi Alex,
Your examples have been really helpful, I was wondering if you could help out a little more.
In this case I’m trying to do an aggreate based on the Previous 30days of the current member
I’m using a Date-Time Hierarchy, so the current member could be a year, quarter, month, day.. so I’d likely be looking at 30days from the lowest value in the current member.
Right now I have one aggretation that does:
Aggregate
(
{[End Date].[Date-Time Tree].CurrentMember:NULL }
,[Measures].[MembersCount]
)
– Though I’d really like to include a ‘[Begin Date] is less than current member as well to avoid counting records that haven’t ‘begun’ yet.
In the end, I’d like to be able to look at a time line and see at any point in time, how many records were ‘current’ (their end date) is on or after that point in time, -30 days past due, -60 days past due and -90 days past due… I figure once I get the 30 it’s just lather, rinse and repeat for any other values I want.
really hope you can help,
Tony
Hi Tony
The first stage would be to identify the leaf member of the hierarchy as the starting point, I.e find the date from whichever level is selected in the hierarchy.
DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0)
You can then use lag to find the previous 30 members and build a set:
{DESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0).lag(30)
ESCENDANTS([End Date].[Date-Time Tree].CurrentMember,,LEAVES).item(0)}
Which you can then put into any calculated member.
Hope this helps
Alex
Alex,
Thank you so much! that just about did it. My numbers are slightly off, but I suspects that’s because I need to use a DistinctCount.
Thank you again for your help!
Tony
Hi Alex
Many thanks for your response! I think I’ve confused things… I meant to convey that I’ve not set up any user-defined defaults on my date dimensions, so I don’t think I’m falling foul of the point you raised re: James’ post above, about having custom default members.
I’m basically trying to follow exactly the approach you described earlier in the post:
AGGREGATE(
YTD([Calendar Production].[Prod - Year - Qtr - Mth].CurrentMember) *
YTD([Calendar Accounting].[Acct - Year - Qtr - Mth].CurrentMember)
, [DateTool].[Aggregation].DefaultMember)
I’m trying to get this working in principle in MDX, before I add it to the utility dimension. Accordingly, I’ve tested the following:
WITH
MEMBER Test1 AS
AGGREGATE(YTD([Date1].[Date].CurrentMember),[Measures].[Count])
MEMBER Test2 AS
AGGREGATE(YTD([Date1].[Date].CurrentMember)*YTD([Date2].[Date].CurrentMember),[Measures].[Count])
MEMBER Test3 AS
AGGREGATE(
YTD(TAIL(DESCENDANTS([Date1].[Date].CurrentMember),,LEAVES),1).Item(0))
* YTD(TAIL(DESCENDANTS([Date2].[Date].CurrentMember),,LEAVES),1).Item(0))
,[Measures].[Count])
SELECT {[Test1],[Test2],[Test3]} ON 0, [Date1].[Date].[Month].MEMBERS ON 1 FROM [Cube]
Test1 works fine, Test2 and Test3 both return Null.
Forgive me if I’m missing something very obvious here – anything you can suggest would be really useful!
thanks
Tim
Hi Tim
Custom default members or not, you still need to pass a valid member into the YTD function. If you haven’t set a custom default, then the default will be All. You can’t do YTD(All), it just doesn’t make sense. You therefore need to ensure that a valid member is always presented to the YTD function, whether the user has selected a member of the date hierarchy or not.
The tail(descentants(… code should ensure that a valid member is always presented. Can you try it and see if it works?