Compare This Year to Last Year with Charts

Author: Joyce Zhou

Introduction

In this article, we'll introduce how to use one filter to control two charts that display the same period within two different years.

Example

The key is using an Application Variable. In this particular case, we need to derive week information from the Fiscal Calendar filter selection using Application Variables, and then use the Lag function over the variable to define the time range on the X axis in the chart.

First, let's define two Standard Application Variables:

Here is the definition for the Week of Selected Day:

EXISTS([Time].[FiscalWeek].[All].Children, ${[Time].[Fiscal Calendar]}).Item(0)

And here is the definition for the Week of Selected Day (Last Year):

EXISTS([Time].[FiscalWeek].[All].Children, ParallelPeriod([Time].[Fiscal Calendar].[FiscalYear], 1, ${[Time].[Fiscal Calendar]})).Item(0)

The axis definition for these two charts is the same.

On the column, we have two hierarchies. The Fiscal Calendar is set to All and we use Range to define the Fiscal Week.

Since the chart displays the past 12 weeks, we can set up the range picker as shown below.

After adding proper members to the column and filters, we will get the Form ready.

Then we can create a chart over the Form.

Follow the same steps to create the chart for last year. The only difference is to use:

 ${Week of Selected Day (Last Year)} 

when defining the Fiscal Week hierarchy on the Row.

And this is the chart you will get.