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

# LOD Rolling Year Over Year by Month

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!

• ###### 1. Re: LOD Rolling Year Over Year by Month

Just bumping this back up. Any help anyone can provide would be greatly appreciated.

• ###### 2. Re: LOD Rolling Year Over Year by Month

May be This:

• ###### 3. Re: LOD Rolling Year Over Year by Month

Hi Deepak,

Thanks for the reply. I'm afraid this didn't quite do the trick. It appears that it solved my previous year problem, but now the current year is showing the same problems as before.

• ###### 4. Re: LOD Rolling Year Over Year by Month

Jacob,

Your calculation will only work on 2017 months because you have a compare with the  {FIXED: MAX(YEAR(Order date))}  which is 2017.

But you can resolve this using the lookup function.

Last year sales would become

LOOKUP( SUM([Sales]),-1 )

computing on  Month , Year  (in that order)

Now for the first month to be able to lookup a month that is filtered out of the view,  your filter needs to be a table calculation. This way the Lookup function will compute before the filtering.

For this you cannot use the quick filters, but you can compute the following

LOOKUP(MIN( DATEDIFF('month', [Order Date], {MAX([Order Date])} )),0 ) < 14

and

LOOKUP(MIN( DATEDIFF('month', [Order Date], {MAX([Order Date])} )),0 ) > 1

and keep the true values.

I used the Datediff function to compute the number of month between the month  and the max month of the dataset  and return true for the months between Nov2016 and Oct2017.

Michel

2 of 2 people found this helpful
• ###### 5. Re: LOD Rolling Year Over Year by Month

Michel, you've saved the day!! This works perfectly. I didn't realize that you could combine table calcs with LODs like this. I'm going to have to experiment with this kind of thing now . Thanks!