I have a data set that only has the FYFM field for the date. It is manually put in by the system, so there is no official "Date" field.
I have a master calendar that I use for creating the TTM (Trailing Twelve Months) and the Current and Prior Fiscal Years.
I cant use that calendar now though because the Date that the calendar uses is trying to convert the Month and Year into fiscal dates, and they are already fiscal.
So my question is, how can I create the TTM and Current/Prior Fiscal year from this field? (Our fiscal year begins in October, ends September)
EXAMPLE (Current date of table, 11/1/16)
Fiscal Year and Month | Current FY | Prior FY | Trailing12 Months |
---|
2015-05 | 2017 | 2016 | 0 |
2015-09 | 2017 | 2016 | 0 |
2015-10 | 2017 | 2016 | 1 |
2016-01 | 2017 | 2016 | 1 |
2016-05 | 2017 | 2016 | 1 |
2016-10 | 2017 | 2016 | 1 |