Trailing Twelve Months Line Graph

[Anonymous]

Hi,

I have a daily table 

Sales:
load * inline [
Date,Month, Department1,  Department2, Amount
1/1/2016, 1, A, G, 6
1/2/2016, 1, A, H, 4
1/2/2016, 1, B, K, 5
1/6/2016, 1, A, G, 7
1/6/2016, 1, A, H, 6
1/6/2016, 1, B, K, 1
1/6/2016, 1, B, L, 2
1/7/2016, 1,3, A, H, 4
1/8/2016, 1,4, B, L, 2
2/15/2016, 2,9, B, L, 2
2/19/2016, 2 ,5

...
10/1/2017, 10, 7, B, K, 1
10/2/2017, 10, 8,  A, H, 6
1/1/2018, 1, 6,  B, L, 2
1/2/2018, 1, 9, A, G, 6
2/1/2018, 2, 7, A, H, 4
];

I need to build the following  line graph:
  - X dimension: month 
  - Y dimension: drill down [Department1, Department2]
  - Measures: trailing 12 months count. Each point of each month should reflect the sum of the last 12 months.

I tried the following but couldn't figure out the right formula.

SUM( {< [Date]={'>=$(=DATE(ADDMONTHS(Max(Date),-12)+1))<=$(=ADDMONTHS(Max(Date),0))'}, [Month]=}>} [Amount])
 
this formula just filter for the last 12 months and break it down by month on my line graph.

Any help would be very appreciated.

Thanks.

[sunny_talwar · Accepted solution]

I suggest using

The As Of Table

or you can look into if you don't want to make changes in the script

Inter Record Functions