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