How to create a pivot table like this

[aresb]

Hi everyone,

i need to create a pivot table where the user input a YearMonth and the table display the last 12 month.

For istance, user select 201210 and the table display from 201110 to 201210.

Something like this

Can someone give me an input to achieve this?

[justISO]

Hi, instead of MonthYear input variable, I suggest to use simple filter. This will be easier, but variable also possible to implement. As you not provided sample data, i'll use mine as an example to generate random 3 year data:

LOAD 
Date(MonthStart(YearStart(Today(), -2), IterNo() - 1) ,'YYYYMM') as YearMonth,
Ceil(Rand() * 100) * 100 as Sales
AutoGenerate 1
WHILE IterNo() <= 36;

From it you create pivot with dimensions and 1 measure you create something like this (other can be simpler):

SUM({$<YearMonth={">=$(=DATE(addmonths(Max(YearMonth),-12),'YYYYMM'))<=$(=Max(YearMonth))"}>} Sales)

In this way, you always see only last 12 months from max date. So if you not filter date, you see last 12 months, if you select any other date, you will see 12 months from that date: