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 tha...
is this in DAX? if it is, can I please have it in SQL.
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.
when I run the query for the whole calendar that starts from 2012, I get below :
