In my cube, they wanted to browse by the week by of the Financial Year.
I've created two new columns: one for the Week of the Year, the other for the Week of the Financial year.
While the first column is easily populated
SET WeekYear = datepart (ww, myDateFormat)
The second one is a bit more complicated:
The year start at 1/7/2013, so I want that week to start as 1. So I need the WeekYear - week number of 1/7/2013.
I added 1 because we start at 1, not 0.
WeekFinancialYear = WeekYear - DATEPART (ww, CAST (CAST([CalanderYear] AS CHAR(4)) + '0701' AS DATE)) + 1
WHERE [month] >=7
But that's for months in the beginning of the financial year, months that their number is bigger than 7.
Otherwise, Just add 26 (this is the number of 1/2 a year).
SET WeekFinancialYear = WeekYear + 26
WHERE [month] < 7
(BTW if you're looking for a job here down under, between the last week of June and the First week of August then you're quite hopeless. Or Week 52 till week 5 of the Financial Year!).