My company uses fiscal calendar for accounting, so the 1st month of is always February, and it ends with next year's January.
For example, we are in 2017 December, but for a Fiscal calendar, we call it Fiscal year 2018 December. The reason it is called Fiscal year 2018 is because it ends on 2018 Jan. And Dec is the 11th month of Fiscal year 2018.
I have no problem to converting the fiscal month to Calendar Month_nbr and Calendar Month_Name as below, however, I can't think of a formula or a work flow for converting the fiscal year to Calendar Year...
Can anyone give some suggestions? Thank you.
Fiscal Year | Fiscal Month | POS | Calendar Year | Calendar Month_NBR | Calendar Month_Name |
2017 | 1 | 2385 | 2016 | 2 | Feb |
2017 | 2 | 1042 | 2016 | 3 | Mar |
2017 | 3 | 4579 | 2016 | 4 | Apr |
2017 | 4 | 8060 | 2016 | 5 | May |
2017 | 5 | 6317 | 2016 | 6 | Jun |
2017 | 6 | 1449 | 2016 | 7 | Jul |
2017 | 7 | 5920 | 2016 | 8 | Aug |
2017 | 8 | 6276 | 2016 | 9 | Sep |
2017 | 9 | 7086 | 2016 | 10 | Oct |
2017 | 10 | 5413 | 2016 | 11 | Nov |
2017 | 11 | 1002 | 2016 | 12 | Dec |
2017 | 12 | 8756 | 2017 | 1 | Jan |
2018 | 1 | 1069 | 2017 | 2 | Feb |
2018 | 2 | 4836 | 2017 | 3 | Mar |
2018 | 3 | 8653 | 2017 | 4 | Apr |
2018 | 4 | 3279 | 2017 | 5 | May |
2018 | 5 | 8129 | 2017 | 6 | Jun |
2018 | 6 | 7281 | 2017 | 7 | Jul |
2018 | 7 | 9353 | 2017 | 8 | Aug |
2018 | 8 | 8890 | 2017 | 9 | Sep |
2018 | 9 | 6319 | 2017 | 10 | Oct |
2018 | 10 | 8039 | 2017 | 11 | Nov |
2018 | 11 | 2391 | 2017 | 12 | Dec |
2018 | 12 | 4613 | 2018 | 1 | Jan |
Solved! Go to Solution.
Pseudo code is simply ...
if [Calendar Month_NBR] = 1
then [Fiscal Year]
else [Fiscal Year] = [Calendar Year]+1
end
Basically, if it's Jan then you're in the right year, otherwise add 1