This article contains a quick list of frequently used MDX scripts and snippets in Kepion projects. Most of these scripts have links to fuller articles explaining their process in more detail, but here they are presented together as a quick reference.
Note: These scripts may need further customization based on your requirements.
Form Design
Calculate Variance For Different Account Types
WITH
MEMBER [Scenario].[Scenario].[Variance] as IIF(
[ACCOUNT].[ACCOUNT].CURRENTMEMBER.PROPERTIES("AccountType")="Income",
[Scenario].[Actual]- [Scenario].[Plan],
[Scenario].[Plan]-[Scenario].[Actual])
Configure Layered Parent-Child Hierarchies
GENERATE(
HIERARCHIZE (DESCENDANTS([Entity].[Entity].[All], 0, AFTER))
,IIF( IsLeaf([Entity].[Entity].CURRENTMEMBER)
,{[Entity].[Entity].CURRENTMEMBER} * { HIERARCHIZE(DESCENDANTS([Product].[Product].[All], 0, AFTER))}
,{[Entity].[Entity].CURRENTMEMBER} * { [Product].[Product].[All] })
)
Remove Aggregation
--
-- Remove Aggregation
--
SCOPE([Measures].[Value]);
[Account].[Account].[No Aggregate] =
IIF
(
-- Check if at Lowest Level
ISLEAF([Entity].[Entity].CurrentMember)
AND ISLEAF([Time].[Fiscal Calendar].CurrentMember)
-- true
,[Entity].[Entity].CurrentMember
-- false
,NULL
);
END SCOPE;
Control Member Order
ORDER(
DESCENDANTS([Time].[Fiscal Calendar].[All], 10000, LEAVES)
,[Time].[Fiscal Calendar].Properties("Id")
,DESC
)
For more information: Control Member Order with MDX, Remove Parent Aggregation with MDX, Configure Layered Parent-Child Hierarchies on Forms
Rolling Forecast
Prior 3 Months
[Time].[Months].&[${Current Month}].Lag(3):[Time].[Months].&[${Current Month}].Lag(1)
Forward 12 Months
[Time].[Months].&[${Current Month}]:[Time].[Months].&[${Current Month}].Lag(-11)
Sum Forward Months
WITH
MEMBER [Time].[Months].[Header_Total] AS 'SUM([Time].[Months].&[${Current Month}]:[Time].[Months].&[${Current Month}].Lag(-11))'
Aggregate Multiple Scenarios
/*-----------------------
Scenario Aggregation
*/-----------------------
//Current FY Actual & Forecast:
//Part 1: Using Actual values for months ranged between January and the month before current
SCOPE
(
[Scenario].[Scenario].[Current FY Actual & Forecast]
,{[Time].[Fiscal Calendar].&[${Current Month}].Parent.Parent.FirstChild.FirstChild
:[Time].[Fiscal Calendar].&[${Current Month}].Lag(1)}
);
THIS = [Scenario].[Scenario].[Actual];
END SCOPE;
//Part 2: Using Forecast values for months ranged between current month and December
SCOPE
(
[Scenario].[Scenario].[Current FY Actual & Forecast]
,{[Time].[Fiscal Calendar].&[${Current Month}]
:[Time].[Fiscal Calendar].&[${Current Month}].Parent.Parent.LastChild.LastChild}
);
THIS = [Scenario].[Scenario].[Forecast];
END SCOPE;
//Rolling Year Forecast: Using Forecast values for current months and next 11 months
SCOPE
(
[Scenario].[Scenario].[Rolling Year Forecast]
,{[Time].[Months].&[${Current Month}]
:[Time].[Months].&[${Current Month}].Lag(-12)}
);
THIS = [Scenario].[Scenario].[Forecast];
END SCOPE;
Calculate YTD
/*------------------------------*/
/* TimeType */
/*------------------------------*/
-- Note: These calculation must be defined for each Time hierarchy that is used. Currently defined for:
-- - Fiscal Calendar
-- - Annual Calendar
Scope
(
{
[Scenario].[Scenario].[Actual]
,[Scenario].[Scenario].[Plan]
}
,[Metric].[Metric].[Amount]
);
/*--------
Previous Week (Rolling 7 Days)
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Previous Week (Rolling 7 Days)]
,Descendants( [Time].[Fiscal Calendar].[All], 3, SELF)
);
THIS = Aggregate(([Time].[Fiscal Calendar].CurrentMember.Lag(6):[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
/*--------
MTD
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Month to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalMonth]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
-- Months - YTD
Scope
(
[Time Type].[Time Type].[Month to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 2, SELF)
);
This = Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalMonth]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
/*--------
YTD
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
-- Months - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 2, SELF)
);
This = Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
-- Years - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 1, SELF)
);
This = [Time Type].[Time Type].[Periodic];
End Scope;
End Scope;
For more information: Configure Rolling Forecast, Calculate WoW, MoM, YoY
Calculate Averages with Hierarchy
Average Non-Empty Leaves / All Leaves
SCOPE
(
{[Account].[Account].[Price]}
,[Measures].[Value]
);
THIS = [Measures].[Value] / [Measures].[Count];
END SCOPE;
Average Non-Empty Children
SCOPE
(
{[Account].[Account].[Cost]},
--SCOPE ALL THE NON-LEAF MEMBERS IN PRODUCT HIERARCHY
{DESCENDANTS([Product].[Product Hierarchy].[All], 3, BEFORE)},
[Measures].[Value]
);
--CALCULATION
THIS = Aggregate([Product].[Product Hierarchy].CurrentMember.Children)
/
COUNT(NONEMPTY([Product].[Product Hierarchy].CurrentMember.Children));
END SCOPE;
Average All Children
SCOPE
(
{[Account].[Account].[Cost]},
--SCOPE ALL THE NON-LEAF MEMBERS IN PRODUCT HIERARCHY
{DESCENDANTS([Product].[Product Hierarchy].[All], 3, BEFORE)},
[Measures].[Value]
);
--CALCULATION
THIS = Aggregate([Product].[Product Hierarchy].CurrentMember.Children)
/
COUNT([Product].[Product Hierarchy].CurrentMember.Children);
END SCOPE;
For more information: Calculate Average With Hierarchy