Introduction
In this article, we'll show you an MDX script you can use to easily calculate your Year to Date (YTD). While this script is designed specifically for YTD calculations, the same principles can applied to calculate Month to Date, Quarter to Date, or other related calculations.
The script attached below contains the full recommended YTD and Total Year calculation logic and an example Application with these calculations configured. If you're using the default Kepion Time Dimension structure, you may need to make changes only to the initial scope statement when adding this to a Model. For an in-depth look at how this calculation works, a step-by-step breakdown of the logic is provided below.
Time Type
The example Model we're going to use has four Dimensions: Account, Time, Scenario, and Time Type. The first three are all basic Dimensions and should be familiar to you. The key Dimension to this type of calculation is Time Type. This Dimension will separate the data into Current Period values and YTD values.
Basic Calculation Structure
We can calculate YTD with the following short script. The following logic serves as the foundation of our full YTD script:
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
Let's break this down, piece by piece. The Scope statement targets the YTD member in the Time Type Dimension, where we want to store the result of the calculation. The Descendants function scopes the calculation to the Month level, the lowest granularity of our sample data.
Tip: The second parameter in the Descendants function determines how far down the hierarchy to traverse. The default levels in the Time Dimension are 1, Year; 2, Quarter; 3, Month.
The PeriodsToDate function returns all of members on the same level of the hierarchy, up to a given point, constrained by a level of the Time Dimension. The first parameter determines the level, while the second determines the last member to include. This is a little easier to understand visually:
This means if we give this function the Fiscal Year level and a May FY21, it will return all the months from Jan FY21 to May FY21—which is what we need to calculate YTD. We then wrap this in an Aggregate function to complete the calculation, making sure to specify that we only want to aggregate the Current Period values.
The end result is a simple YTD calculation:
Calculating Parent Levels
While this calculation seems to work, it still has a few problems. The first issue is easy enough to identify if we add the Fiscal Quarter and Fiscal Year members to the report:
The value for Q1 is the sum of its children (100 + 200 + 300) rather than the YTD value (300). The same is true for the Fiscal Year value. This is because the script is currently scoped to calculate the YTD values only at the month level.
The fix, however, is simple. All we need to do is repeat the same calculation at the higher levels of the hierarchy:
--Months
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
--Quarters
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 2, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
--Years
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 1, SELF)
);
This = [Time Type].[Time Type].[Current Period];
End Scope;
There are only two key differences to notice in the repeated calculations. The first is that in the Scope statement, the Descendants function has been modified to traverse to level 2 for quarters and 1 for year. And secondly, in the years calculation, notice that the Aggregate and PeriodsToDate functions have been removed. This is because there's only one number at the year level, so there's nothing to aggregate.
Scoping the Calculation
The last step in putting together this calculation is ensuring that it's scoped properly. Scoping calculations is important for both data integrity and performance. Performance can be impacted by calculating YTD across members that don't make sense in a YTD context, such as percentages or rates.
Depending on the structure of your Model, improperly accounting for all the dimensions in the scope statement can cause the calculation to return incorrect results. Let's take a look at three important ways we can expand the way this calculation is scoped.
Account and Scenario
The two Dimensions we haven't yet accounted for in this calculation are Account and Scenario. As we saw above, failing to scope the Account Dimension will cause the calculation to be performed across ALL members. We don't want that, so let's apply it to just the members that make sense. We'll do the same for the Scenario Dimension.
Scope(
{[Scenario].[Scenario].[Actual], [Scenario].[Scenario].[Budget], [Scenario].[Scenario].[Forecast]}
,Descendants([Account].[Account].[Income Statement], 10000, LEAVES)
);
-- For Months
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Quarters
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 2, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Years
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 1, SELF)
);
This = [Time Type].[Time Type].[Current Period];
End Scope;
End Scope;
Your Application will likely have to account for more dimensions and members. When building this scope statement, make sure you ask yourself whether a particular member should be included when calculating YTD.
Fiscal and Annual Calendars
Up until now we've only accounted for the Fiscal Calendar in our calculation. This means, if you use the Annual Calendar Hierarchy on a report, it won't have the YTD calculation available. We can easily fix this by simply duplicating the calculation a second time and changing the hierarchy from Fiscal to Annual.
Scope(
{[Scenario].[Scenario].[Actual], [Scenario].[Scenario].[Budget], [Scenario].[Scenario].[Forecast]}
,Descendants([Account].[Account].[Income Statement], 10000, LEAVES)
);
/************************************/
/* Fiscal Calendar */
/************************************/
Scope(
[Time].[Annual Calendar].[All]
);
-- For Months
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Quarters
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 2, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Years
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 1, SELF)
);
This = [Time Type].[Time Type].[Current Period];
End Scope;
End Scope;
/************************************/
/* Annual Calendar */
/************************************/
Scope(
[Time].[Fiscal Calendar].[All]
);
-- For Months
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Annual Calendar].[All], 3, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Annual Calendar].[Year]
,[Time].[Annual Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Quarters
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Annual Calendar].[All], 2, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Annual Calendar].[Year]
,[Time].[Annual Calendar].CurrentMember
)
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Years
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Annual Calendar].[All], 1, SELF)
);
This = [Time Type].[Time Type].[Current Period];
End Scope;
End Scope;
End Scope;
Note that we also added another scope statement to each section, however it may look confusing to see the Fiscal Calendar calculation scope to [Time].[Annual Calendar].[All] and vice versa for the Annual Calendar. This is because by explicitly defaulting the Annual Calendar Hierarchy to the All member it excludes the leaf members from the Fiscal Calendar definition.
Time Hierarchies
Lastly, there are certain Form designs that can produce incorrect results, particularly when the Time Dimension is used on multiple axes. To account for this problem, we need to explicitly include each of the attribute hierarchies that we'll be using throughout the calculation. Some of the most common are included in this script below:
//-----------------------------
// Time Type
//-----------------------------
-- Note: These calculation are defined by either Fiscal or Annual aggregation:
-- Fiscal:
-- Leveled Hierarchy: "Fiscal Calendar" -> FiscalYear | FiscalQuarter | FiscalMonth | MemberId
-- Related Attributes: FiscalYear, FiscalQuarter, FiscalMonth, FiscalMonthOfYear, FiscalQuarterOfYear
-- Annual:
-- Leveled Hierarchy: "Annual Calendar" -> Year | Quarter | Month
-- Related Attributes: Year, Quarter, Month, MonthOfYear, QuarterOfYear
Scope(
{[Scenario].[Scenario].[Actual], [Scenario].[Scenario].[Budget], [Scenario].[Scenario].[Forecast]}
,Descendants([Account].[Account].[Income Statement], 10000, LEAVES)
);
//---------------------------
// FISCAL
//---------------------------
-- default attribute hierarchies to [All] to ensure these calculations
-- only impact the targeted time slices
Scope(
[Time].[Year].[All]
,[Time].[Quarter].[All]
,[Time].[Month].[All]
,[Time].[MonthOfYear].[All]
,[Time].[QuarterOfYear].[All]
);
//--------
// YTD
//--------
-- For Months
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
*{[Time].[FiscalMonthOfYear].[All]}
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Quarters
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 2, SELF)
,[Time].[FiscalMonthOfYear].[All] // set to all as calculations for months need to be preserved
);
This = Aggregate(
PeriodsToDate(
[Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember
)
*{[Time].[FiscalQuarterOfYear].[All]}
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Years
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Fiscal Calendar].[All], 1, SELF)
,[Time].[FiscalMonthOfYear].[All] // set to all as calculations for months need to be preserved
,[Time].[FiscalQuarterOfYear].[All] // set to all as calculations for quarters need to be preserved
);
This = [Time Type].[Time Type].[Current Period];
End Scope;
End Scope;
//---------------------------
// ANNUAL
//---------------------------
-- default attribute hierarchies to [All] to ensure these calculations
-- only impact the targeted time slices
Scope(
[Time].[FiscalYear].[All]
,[Time].[FiscalQuarter].[All]
,[Time].[FiscalMonth].[All]
,[Time].[FiscalMonthOfYear].[All]
,[Time].[FiscalQuarterOfYear].[All]
);
//--------
// YTD
//--------
-- For Months
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Annual Calendar].[All], 3, SELF)
);
This = Aggregate(
PeriodsToDate(
[Time].[Annual Calendar].[Year]
,[Time].[Annual Calendar].CurrentMember
)
*{[Time].[MonthOfYear].[All]}
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Quarters
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Annual Calendar].[All], 2, SELF)
,[Time].[MonthOfYear].[All] // set to all as the calculation for months need to be preserved
);
This = Aggregate(
PeriodsToDate(
[Time].[Annual Calendar].[Year]
,[Time].[Annual Calendar].CurrentMember
)
*{[Time].[QuarterOfYear].[All]}
,[Time Type].[Time Type].[Current Period]
);
End Scope;
-- For Years
Scope(
[Time Type].[Time Type].[YTD]
,Descendants([Time].[Annual Calendar].[All], 1, SELF)
,[Time].[MonthOfYear].[All] // set to all as the calculation for months need to be preserved
,[Time].[QuarterOfYear].[All] // set to all as the calculation for quarters need to be preserved
);
This = [Time Type].[Time Type].[Current Period];
End Scope;
End Scope;
End Scope;
The script above is a complete, robust, YTD calculation. While the end result is quite long, remember that it's primarily the basic calculation structure from the beginning, repeated over and over with small Scope changes.
If your Application is using the standard Time Dimension, you can likely calculate YTD quickly. Just add this script directly to a Model and change the initial scope statement to account for your Model's Dimension Members.