Period by growth period for a dynamic measure
I wrote a period mdx
script showing the growth period for internet sales volume and everything works fine. We are using an interface where you can place a slicer so that the user can choose which dimension of the date. The calendar he is interested in, regardless of the Date.Calendar dimension that the user decides to select (quarter, month, year), he will look correctly at the previous participant. Now I'm trying to get the same thing for Measure - that is, I want it to be flexible depending on which measure the user chooses in the slicer (i.e. Internet Tax Amount).
I can't think of a way to create the previous member with measure flexibility ...
WITH
MEMBER [Measures].[Internet Sales Amount PP] AS
(
[Date].[Calendar].CurrentMember.Prevmember,
[Measures].[Internet Sales Amount]
)
MEMBER [Measures].[Period on Period Grwth %] AS
IIF (
[Measures].[Internet Sales Amount PP] = 0,
'N/A',
([Measures].[Internet Sales Amount]-[Measures].[Internet Sales Amount PP]) /[Measures]. [Internet Sales Amount PP]
)
,FORMAT_STRING = "percent"
SELECT
[Date].[Calendar].[Month] ON ROWS,
{[Measures].[Internet Sales Amount],
[Measures].[Internet Sales Amount PP],
[Measures].[Period on Period Grwth %]} ON COLUMNS
FROM [Adventure Works]
source to share
If you are using SSRS, you can add an option to absorb the measure selected in the dropdown. We host SSRS in a SharePoint environment and we do it.
WITH
MEMBER [Measures].[Measure Growth PP] AS
(
[Date].[Calendar].CurrentMember.Prevmember,
strtomember('[Measures].'+ @MeasureSelected)
)
MEMBER [Measures].[Period on Period Grwth %] AS
IIF (
[Measures].[Measure Growth PP] = 0,
'N/A',
(strtomember('[Measures].'+ @MeasureSelected)-[Measures].[Measure Growth PP]) /
[Measures].[Measure Growth PP]
)
,FORMAT_STRING = "percent"
SELECT
[Date].[Calendar].[Month] ON ROWS,
{strtomember('[Measures].'+ @MeasureSelected),
[Measures].[Measure Growth PP],
[Measures].[Period on Period Grwth %]} ON COLUMNS
FROM [Adventure Works]
source to share