Calculate WoW, MoM, and YoY

Author: Joyce Zhou

Introduction

In this article, let's talk about how to create a report with Week Over Week (WoW), Month Over Month (MoM), and Year Over Year (YoY) comparisons. The column definition in this Form is pretty complicated. We can easily tell we have Time and Scenario on the column.

What makes it even more interesting is that the Change columns are not the variance between the Actual and Plan scenarios, otherwise we would be able to easily get this done using a scenario formula. Instead, it's the variance of Actual data compared to the previous time period (Week, Month, or Year). The approach we'll take instead is to include a third Dimension on the column: Metric.

Example

First, let’s get the Form to look exactly how we want. Here is the COLUMN definition:

There are some value overrides in the column headers:

With some formatting, we'll get the Form structurally ready.

Then let’s start to work with the data. First let’s add the MDX calculation for Time Type.

/*------------------------------*/
/* 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], 4, 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], 4, 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], 3, 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], 4, 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], 3, 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;

This is the Form we get at this step.

Next let’s add in the MDX calculation for the Change.

Scope
(
[Scenario].[Scenario].[Actual]
,[Metric].[Metric].[Change]
);

/*--------
Previous Week (Rolling 7 Days)
*/--------

-- Days - YTD
Scope
(
[Time Type].[Time Type].[Previous Week (Rolling 7 Days)]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);

THIS = Aggregate([Time].[Fiscal Calendar].CurrentMember
,[Metric].[Metric].[Amount])
/
Aggregate([Time].[Fiscal Calendar].CurrentMember.Lag(6)
,[Metric].[Metric].[Amount])
- 1;

End Scope;

/*--------
MTD
*/--------

-- Days - YTD
Scope
(
[Time Type].[Time Type].[Month to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalMonth]
,[Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalMonth]
,ParallelPeriod([Fiscal Calendar].[FiscalMonth], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;

-- Months - 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)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalMonth]
,ParallelPeriod([Fiscal Calendar].[FiscalMonth], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;

/*--------
YTD
*/--------

-- Days - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalYear]
,ParallelPeriod([Fiscal Calendar].[FiscalYear], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;

-- Months - 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)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalYear]
,ParallelPeriod([Fiscal Calendar].[FiscalYear], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;

-- Years - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 1, SELF)
);
This = ([Time].[Fiscal Calendar].CurrentMember
,[Metric].[Metric].[Amount])
/
(ParallelPeriod([Fiscal Calendar].[FiscalYear], 1, [Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
- 1;
End Scope;

End Scope;

Adding in the KPI, we get the report we want to create.