Year Ago Calculation Without Time Series Functions


This article explains you how to calculate year ago measures with out 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 Fx :

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

Hope it’ll helpful to you..

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)


Kishore Guggilla.



About Kishore & Naresh

Hi.. This is a blog operated by Kishore and Naresh. We are colleagues and working as BI Developers. Here we want to share our ideas and researches here. We were the active members of OTN Forums and inspired with Venkat and John's blog. We may not reach to that level, but we brings up all our ideas and research work we done. All the views expressed here are our own and does not reflect the views of our employers. We need all your blessings and support to grow. Thanks

3 responses »

  1. I want to do a simple calculation of (A-B)/B when A is coming from first query in union report and B is coming from Second Query.
    Basically I am trying to see if there is a way to do calculation on top of a union report.

    • Deepika,

      if you’re using union, why don’t you go for Resultant columns. Add a resultant column then change it’s fx to desired code by selecting those resulting columns i.e. A,B.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s