Calculated members based on interrelationship of two time dimensions

Question

This is an "is it possible" kind of question. To illustrate what I am asking about I have attached a partial screen shot of a PivotTable. The rows and columns of the PivotTable represent two different time role-plays. My measure is called "Point Of Sale". The time dimension on rows is called "Attributed Date". The time dimension on the columns is called "Filing Period". The time dimensions have a granularity of month.

The formatting of the PivotTable values represents the three distinct calculated members I would want to create. The simplest of the three measures is represented by the boxes with borders and without shading. These are the places where Attributed Date and Filing Date (Year-Month) are the same. Let's call these "on-time filings".

The other two measures are based on multiple cells in the cube and would calculated across the Attributed Date dimension which are the rows in the PivotTable example. I would want a measure for "late filings" which is represented by the yellow shaded cells and is the sum of all cube cells that have a Filing Date earlier than their Attributed Date. The other other measure I will call "early filings" represented by the pink cells in the PivotTable which have a FilingDate later than the Attributed Date.

I would think it would be possible to do this, but so far it has proven beyond my skill level. Any suggestions from the forum, or an explanation of why this is not possible will be much appreciated.

Answer 1

The key function you're looking for is the LinkMember function. Your OnTime Filings measure would look something like the following:

WITH MEMBER [Measures].[OnTime Filings] AS 
	CASE 
		WHEN [Attributed Date].[Calendar].CurrentMember IS NOT [Attributed Date].[Calendar].[All] THEN
			(
				[Measures].[Point of Sale],
				LinkMember(
					[Attributed Date].[Calendar].CurrentMember,
					[Filing Date].[Calendar]
				)
			)
		WHEN [Filing Date].[Calendar].CurrentMember IS NOT [Filing Date].[Calendar].[All] THEN
			(
				[Measures].[Point of Sale],
				LinkMember(
					[Filing Date].[Calendar].CurrentMember,
					[Attributed Date].[Calendar]
				)
			)
		ELSE NULL
	END 

Similarly for the early filings, the formula would look something like the following where the set of [Filing Date] Months prior to the current [Attributed Date].[Calendar] member are aggregated over or the [Attributed Date] months subsequent to the current [Filing Date] member are aggregated over.

WITH MEMBER [Measures].[Early Filings] AS 
	CASE 
		WHEN [Attributed Date].[Calendar].CurrentMember IS NOT [Attributed Date].[Calendar].[All] THEN
			SUM( { NULL : LinkMember(
					[Attributed Date].[Calendar].CurrentMember,
					[Filing Date].[Calendar]
				).Lag(1) },
				( [Measures].[Point of Sale] )
			)
		WHEN [Filing Date].[Calendar].CurrentMember IS NOT [Filing Date].[Calendar].[All] THEN
			SUM( { LinkMember(
				[Filing Date].[Calendar].CurrentMember,
				[Attributed Date].[Calendar]
				).Lead(1) : NULL },
				( [Measures].[Point of Sale] )
			)
		ELSE NULL
	END 

No guarantee this formula will work in all cases but hopefully it will kick start your eventual solution.

HTH, Martin

---

http://martinmason.wordpress.com

Answer 2

Thanks, Martin. That was a good kick start. Here is what I have so far.

WITH MEMBER [Measures].[OnTime Filings] AS 
CASE 
WHEN NOT([Attributed Date].[Year Month].CurrentMember IS [Attributed Date].[Year Month].[All]) THEN
(
[Measures].[Point of Sale],
LinkMember(
[Attributed Date].[Year Month].CurrentMember,
[Filing Period End Date].[Year Month]
)
)
WHEN NOT([Filing Period End Date].[Year Month].CurrentMember IS [Filing Period End Date].[Year Month].[All]) THEN
(
[Measures].[Point of Sale],
LinkMember(
[Filing Period End Date].[Year Month].CurrentMember,
[Attributed Date].[Year Month]
)
)
ELSE NULL
END 
MEMBER [Measures].[Late Filings] AS 
CASE 
WHEN NOT([Attributed Date].[Year Month].CurrentMember IS [Attributed Date].[Year Month].[All]) THEN
SUM( { NULL : LinkMember(
[Attributed Date].[Year Month].CurrentMember,
[Filing Period End Date].[Year Month]
).Lag(1)},
( [Measures].[Point of Sale] )
)
WHEN NOT([Filing Period End Date].[Year Month].CurrentMember IS [Filing Period End Date].[Year Month].[All]) THEN
SUM( { LinkMember(
[Filing Period End Date].[Year Month].CurrentMember,
[Attributed Date].[Year Month]
).Lead(1) : NULL },
( [Measures].[Point of Sale] )
)
ELSE NULL
END
MEMBER [Measures].[Early Filings] AS 
CASE 
WHEN NOT([Attributed Date].[Year Month].CurrentMember IS [Attributed Date].[Year Month].[All]) THEN
SUM( {LinkMember(
[Attributed Date].[Year Month].CurrentMember,
[Filing Period End Date].[Year Month]
).Lead(1) : NULL},
( [Measures].[Point of Sale] )
)
WHEN NOT([Filing Period End Date].[Year Month].CurrentMember IS [Filing Period End Date].[Year Month].[All]) THEN
SUM( { NULL : LinkMember(
[Filing Period End Date].[Year Month].CurrentMember,
[Attributed Date].[Year Month]
).Lag(1)},
( [Measures].[Point of Sale] )
)
ELSE NULL
END
select {[Measures].[OnTime Filings], [Measures].[Early Filings], [Measures].[Late Filings]} on columns,
[Attributed Date].[Year Month].members on rows
from [County Option]

The result looks like that below. When I check it, it appears to correct except for a "January anomaly". Take 2010 January as an example. The Late Filings number is correct for January. But the Early Filings comes back null, and the OnTime Filings figure is the overall total for the year, rather than just the On Time filings total. Any idea why January is wrong while the other months are OK?

Answer 3

Does the key for your month attribute include both Year and MonthNo or just the MonthNo? By the looks of your results, I'm guessing that your month attribute by itself only has 12 members and not 12 per year.

HTH, Martin

---

http://martinmason.wordpress.com

Answer 4

Thanks Martin. Again you pointed me in the right direction. It turns out that my date hierarchy was using the beginning of period date as the key for both year and month. That means January and its respective year had duplicate key values. I fixed that and the 'January anomaly" is gone.