4 Replies Latest reply: Mar 28, 2016 7:26 AM by Rameshwar Pawale RSS

    Rolling window comparision

    Rameshwar Pawale

      Hi All,

       

      I wanted to create a table which will compare the metrics value with current year/quarter, previous year/quarter and previous to previous quarter based on user selection.

       

      Please refer below screenshot which is created in Power-Pivot -

      Emea.jpg

       

       

      User selection => Calendar Year = 2014 and Month-Name = 'March'.

      First table shows data Quarter-wise where-in [March is the end interval]

      I should see data from January to March for current year(2014), same period last year and last to last year.

       

      Second table shows data Year-wise where-in [March is the end interval]

      I should see data from April to March for current year(2013-14), same period last year and last to last year.

       

      I am able to write Set Analysis Expression for calculating data Year-wise using the following Set Analysis -

       

      Sum({<[Calendar Year] = {$(=ONLY([Calendar Year]) - 1)}>}[Metrics1])    -- Previous Year

      Sum({<[Calendar Year] = {$(=ONLY([Calendar Year]) - 1)}>}[Metrics1])    -- Previous to Previous Year


      But I am not sure how to use Month Name in order to go back for last 3 months. I tried below expression

       

      Sum({<[MonthName]={'>=$(=MAX([MonthName])-2)<=$(=MAX([MonthName]))'}>}[Metrics1]).

       

      Also, how to handle the Jan and Feb month where-in we need to go back an year. Like, when I select Feb 2012, then I should see data from Dec 2011 to Feb 2012.

       

      Thank you for your help.