Forum Discussion
SQL Calculate Financial week
Hi,
I would like to calculate financial week in my calanedar table. Financial year starts from July and ends in June.
I have some rules around the final week in the financial year.
if the week that has June and July days has 3 or more days in June then it is week 53, and then reset the followig week to 1 like be below dates:
if the week that has June and July days has 3 or more days in July then it is week 1, and then reset the followig weeks accordinally like be below dates:
Can you please help?
thanks.
6 Replies
Anonymous , these are few columns you need
Start Year = STARTOFYEAR('Date'[Date],"6/30") //
Start Year = if(month([Date]) <7, YEAR([Date]) -1, YEAR([Date]) )
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //mondayor
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
FY Year = YEAR('Date'[Start Year]) // use end year
FY Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[FY Year]=EARLIER([FY Year])),'Date'[Start of Week]),[Date],DAY),7)+1Some of them you can merge in one column using var
Anonymous , Hope you have a date and FY start, and FY year column
for FY
case when datepart(Month,[Date])<7 then datepart(Year, [Date])-1 else datepart(Year, [Date]) end
FY week
datediff(Day , Min(Dateadd(Day, -1* DATEPART(WEEKDAY, [Date]) +1, [Date] )) OVER (PARTITION BY [FY Year] ) , [Date])/7
amitchandak , thanks for your reply.
I do have FY, but not sure what do you mean by FY start? and where do you use FY start?
I have created the FY week based on your sql, but it is not correct, I get below:
why do I get 0 for 20 and 21 June, it should be week 51?
and only 29 and 30 of June labeled week 2.
