Create a Fiscal Calendar with non-standard dates

[#1]

Good morning,

I've found a few discussions and blog posts which state how to create a Fiscal Calendar where they run from the beginning of the month to the end of the month, but I haven't yet found anything that can help me to solve my requirement, so I'm hoping someone can help me out.

Our Fiscal Year runs from January 1st to December 31st, but each Fiscal Period ends on the last Friday of each month, with the exception of Period 12, which ends on December 31st.

For 2015, this looks like the following (DD/MM):

Period 1: 01/01- 30/01

Period 2: 31/01 - 27/02

Period 3: 28/02 - 27/03

Period 4: 28/03 - 24/04

Period 5: 25/04 - 29/05

Period 6: 30/05 - 26/06

Period 7: 27/06 - 31/07

Period 8: 01/08 - 28/08

Period 9: 29/08 - 25/09

Period 10: 26/09 - 30/10

Period 11: 31/10 - 27/11

Period 12: 28/11 - 31/12

Can anyone tell me how I can write a script that will automatically create the Fiscal periods for each year automatically?

[hic · Accepted solution]

One way to do it is the following:

Load Num(
If(Date>FiscalPeriodEnd, CalendarMonth+1,
If(Date<FiscalPeriodStart, CalendarMonth-1,
CalendarMonth))) as FiscalPeriod,
*;
Load
If(Month(Date)=1, MonthStart(Date),Date(MonthStart(Date)-WeekDayMonthStart)) as FiscalPeriodStart,
If(Month(Date)=12, Date(Floor(MonthEnd(Date))),Date(Floor(MonthEnd(Date)-WeekDayMonthEnd))) as FiscalPeriodEnd,
*;
Load
Dual(WeekDay(MonthEnd(Date)), Mod(3+WeekDay(MonthEnd(Date)),7)) as WeekDayMonthEnd,
Dual(WeekDay(MonthStart(Date)), Mod(2+WeekDay(MonthStart(Date)),7)) as WeekDayMonthStart,
WeekDay(Date) as WeekDay,
Month(Date) as CalendarMonth,
Year(Date) as CalendarYear,
*;
Load Date(RecNo()-1+MakeDate(2000)) as Date Autogenerate 10000;

HIC

[#3]

Thanks so much Henric! That answer is perfect - it does exactly what I'm after.

[#4]

Hi hic‌ jamie.maguire‌ ,

I am looking similar type of fiscal calendar. every period starts on monday and ends on sunday .

Can anyone tell me how I can write a script that will automatically create the Fiscal periods for each year automatically?

Thanks

Venkat hic‌

[#5]

hic‌

Condition: Each period starts on Monday which is nearer to 1st of that month.

Thanks

Venkat