Just want to clarify the training 12 months definition in your context.
If you are in December 2017 and assuming your (financial) year starts in January you would want Year to Date total for 2017 in TTM. In this case, the previous years total would not change at all? or am I misunderstanding the requirement.
ShivaRam Chennapragada wrote:
... For confidentiality purposes I am unable to share the workbook, apologies!
We don't need your exact data. An excel-based workbook with 8 years of data with two columns: [Date] and [Value] would be enough to demonstrate what you have and what someone can do with it.
It would help me see how your dates are set up, for example. (Exact dates? Month-and-year only? Etc .) And how your sheet is set up. We're going to need table calcs to look back 12 months, for example. And those often depend on what other dimensions are in play on the sheet.
To get you started, though, if you have dates that include the calendar day of the month, you can simplify the data using DATETRUNC('month',[your date field]). This truncates all dates to the first day of the respective month. Sometimes this makes calcs on date ranges a whole lot easier if you are working at the month level.
WINDOW_SUM(SUM([Current]), -11,0) will get you the prior 11 months plus the current month (for a total of 12 months) if your table is partitioned by month. Or WINDOW_SUM(SUM([Current]), -12,-1) will get you the 12 month before the current month.
Hi Sujay, because December is not ended yet, in my context trailing 12 months would be going back 12 months from "last completed month" i.e., November. So it would be from Dec, 2016 through Nov, 2017 (both inclusive). This would be same for all the years going back. If I'm in January 2018 then yes, my trailing 12 months would be one full year(2017) but when I move into Feb - it would be Feb 2017 - Jan 2018. Hope this gives you clarification.