SQL Calculate Financial week

[Anonymous]

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.

[Anonymous]

is this in DAX? if it is, can I please have it in SQL.

[amitchandak]

@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

[Anonymous]

@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.

[Anonymous]

when I run the query for the whole calendar that starts from 2012, I get below :

[amitchandak]

@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 //monday

or

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)+1

Some of them you can merge in one column using var

[Anonymous]

Hi @amitchandak ,

Can I have that in SQL please?