WoW, MoM and YoY Calculation

By - Jun 22, 2015

In this article, we will talk about how to create a report with Week Over Week (WoW), Month Over Month (MoM), and Year Over Year (YoY) Comparisons.

WoW, MoM, YoY Comparisons

The column definition on this form is pretty complicate. We can easily tell there are time type and scenario on the column. What makes it even more interesting is that the “Change” columns are not the variance between Actual and Plan scenario, otherwise we can easily get this done by using scenario formula. Instead, it is the variance on Actual data compared with the previous time period (Week, Month, or Year). The approach we take is to include a third dimension on the column: Metric.

First step, let’s get the form look exactly as what we need. Here is the column definition.

Define Form Column

There are some value override in the column header.

Define Column Header

Define Column Header

With some formatting, we will get a form structurally ready.

WoW, MoM, YoY Comparisons

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

This is the form we get at this step.

WoW, MoM, YoY Comparisons

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

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

WoW, MoM, YoY Comparisons with KPI