I'm trying to get the fiscal month end date for dates out of an SAP table. For example, for 4/5/2019 the fiscal period end date is 5/4/2019. I can see how to get month end, but that doesn't work for what I need. I was able to get week ending date and those dates are below as an example of what I'm working with.
Solved! Go to Solution.
Hi there @tlkirby
Can you provide a more detailed formula to calculate the fiscal month end, preferable with a few, unambiguous examples. In your example, "4/5/2019(5th of April?) the fiscal period end date is 5/4/2019(May 4th?)" is the fiscal month end always the 4th day of following month?
Dan
Hi! Our fiscal calendar is set up January through December. January has 5 weeks, February and March have 4 and the months repeat in that format. The last day of January 2019 is Feb 2, February 2019 last day is March 2, March 2019 last day is March 30, and so forth following the 5/4/4 format. Since there is not a specific date that ends the month, this might not be possible.
Just to be clear on this point, the week that January 1st falls in is called week 1 and week 5 ends 5 Saturdays after that, Feb 2 in 2019. Is this correct?
Also, your 5/4/4 scheme for 2019 ends on Dec 28. So Dec 29, 30, 31 are in fiscal Jan 2020?
Dan
Yes, January 1-5 is week one. For this report, December 29-31 is week 53 of 2019.
So December 29-31 are in fiscal December 2019?. And fiscal December 2019 finishes on Dec 31st?
Dan
Yes, December 29 through 30 are Fiscal 19
Here you go. It was a tricky one. Fun, though!
Results, scrolled to show how Feb 01 and 02 are in Fiscal Jan
The work flow only does one year at a time because of the the initial start date. You can either convert it to a macro and run it dynamically or run it for the next 20 years and store the results in a .yxdb.
Note that your example db only had the Week ending dates in it, so I added a generate rows tool called "Input days" to generate more sample data.
Dan
Thank you so much! I will look at the flow ASAP