5 Replies Latest reply on Nov 30, 2018 6:44 AM by Jacob Turner

    LOD Rolling Year Over Year by Month

    Jacob Turner

      Hi folks,

       

      I'm trying to create a rolling 12 month view, but I need to include last year values for the same months so that I can color and label my viz based on the YoY change.  I found a beautiful calculation on another post that got me halfway there:

       

      { FIXED MONTH([Order Date]) : SUM(IF YEAR([Order Date]) = {MAX(YEAR([Order Date]))}-1 THEN [Sales] END )}

       

      but the problem is that because of that 'MAX' in the calc, it only ever looks at the latest year, but because this is a rolling 12 months, it will almost always contain several months from the previous year as well. Here's an example of what I mean:

       

      In this case, all of the months from 2017 work fine because that's the latest year in the data, but once it gets to the 2016 months it no longer gives me the previous year's data (I need the 2015 numbers here).

       

      I've tried as many variations of this as I can think of and I'm just plain stuck. Any help you folks can provide would be greatly appreciated. I've attached a workbook here if it helps.

       

      Thanks!