Need to calculate first Monday in February for fiscal calendar

[markgraham123]

Hello Everyone,

I'm trying to create a master calendar with fiscal weeks and year.

My fiscal week should start from First Monday of February as Fiscal Week 1.

So, i wanna automate something which should not change with respect to year.

Any help is highly appreciated!

I tried the following, but no luck. It changes year by year.

if(week(TempDate) > 5,week(TempDate)-5, week(TempDate) + 47) As [Fiscal Week],

if((week(TempDate) < 5), (year(TempDate)-1), year(TempDate)) as [Fiscal Year],

[sunny_talwar · Accepted solution]

Give this a try:

Table:

LOAD *,

  If(Week(Date, 0)< Week([First Monday]), Week(Date, 0) + 47, Week(Date, 0) - 5) as [Fiscal Week],

  Week(AddMonths(Date, -1) - Day([First Monday]) + 1) as [Fiscal Week1],

  'FY' & If(Month(Date) = 'Feb' and Day(Date) < Day([First Monday]), Year([First Monday]) - 1, Year([First Monday])) as [Fiscal Year];

LOAD Date,

  WeekStart(MonthStart(YearStart(Date, 0, 2)) + 6, 0, 0) as [First Monday],

  MonthName(Date) as MonthYear;

LOAD Date(MakeDate(2013,12,31) + RecNo()) as Date

AutoGenerate 1460;