How to retrieve previous year’s march data for a particular measure?

 

For example: if current year is 2016 and we need to retrieve data for March-2015. In the coming year i.e 2017, we would need March-2016 data and so on.


Solution: This can be achieved by using the relative time series feature and filter.


  • Create cross tab with two measure as shown below,


  • Rename the column headers to LY-March for [GrossSales] & Last Three Months for [GrossSales_1]
  • Add relative time series to the measure LY-March [GrossSales] as Year-1 as shown below.





  • Similarly, add relative time series to the measure Last Three Months [GrossSales_1] as Year, Month, Month-1, Month-2
  • Create a filter to get only March month’s data. (Here filter condition to be created is Month = 12 for using financial year which starts from April.)
  • Now click on “Apply condition to below columns” and apply this filter condition to required column (Here filter is applied on LY-March [GrossSales] column).




  • The resultant crosstab would be.


Note: This article is based on ElegantJ BI Version 4.3. This may or may not be relevant to the ElegantJ BI version you may be using.