Year Ago Calculation without Time Series Functions

This article explains you how to calculate year ago measures without using time-series function.

Usually, for requirement like: measure, year ago measure want to show. We’ll go for 2 measures. One is: Measure and YearAgoMeasure calculated through time-series.

This blog entry also avoid to create other column for calculating year ago measure..

Here is the procedure:

I’m assuming you have Year dashboard Prompt, assigned with presentation variable:  var_Year

1. Create a filter on year column in this way:

2. And filter should be like this:

3.  Go to Pivot then arrange columns in this fashion and view Results:

You’ll be noticed that results are giving current/ (selected year from prompt) and previous year values.

Note: Apply descending order on Year column.

You may not like the labels showing year values. Instead of that, you may need to see the labels like: Current and Prior..

This can be done by writing following condition in Year Ex :

CASE WHEN Time.”Fiscal Year” = @{var_Year} then ‘Current’ when Time.”Fiscal Year” = @{var_Year}-1 then ‘Prior’ END

Limitation with this approach is: we’re using Advanced filter, which will not appear dynamically changing years in filters view (if you add filter view to compound layout)