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.
