One of the most common calculations is calculating the percentage of the current value to the total one level up. For instance [Measures].[Sales Amount] for the quarter compared to the semester.
This gives use several challenge's.
1. What is the total amount one level up. With the .CurrentMember property we know where we are. If we combine this with the .Parent property we can calculate the total amount: ([Measures].[Sales Amount],[Date].[Calendar].CurrentMember.Parent) .
Showing posts with label MEMBER. Show all posts
Showing posts with label MEMBER. Show all posts
Sunday, August 3, 2014
Monday, July 28, 2014
MDX using member
The member function in MDX is comparable with the MS-SQL CTE functionality. It allows you to define a member before you use it in your query and thus keeping your query more readable.
An example:
WITH MEMBER [Measures].[Prior Year Sales] AS
SUM(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
)
,
[Measures].[Sales Amount]
)
This calculates the SUM of the Sales Amount for the previous year using the ParallelPeriod functionality in combination with CurrentMember.
An example:
WITH MEMBER [Measures].[Prior Year Sales] AS
SUM(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
)
,
[Measures].[Sales Amount]
)
This calculates the SUM of the Sales Amount for the previous year using the ParallelPeriod functionality in combination with CurrentMember.
Subscribe to:
Posts (Atom)