How do I get fiscal month end date for a regular calendar date?

[tlkirby]

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. 

[danilang]

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

[tlkirby]

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. 

[danilang]

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

[tlkirby]

Yes, January 1-5 is week one. For this report, December 29-31 is week 53 of 2019.

[danilang]

So December 29-31 are in fiscal December 2019?.   And fiscal December 2019 finishes on Dec 31st?

Dan

[tlkirby]

Yes, December 29 through 30 are Fiscal 19

[danilang]

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

[tlkirby]

Thank you so much! I will look at the flow ASAP

[danilang · Accepted solution]

New WF with a small modification.  The original returned the fiscal month but not the end date of the fiscal month

Dan