Calculating the Week Number for the Australian Financial Year

In Australia, the Financial Year Starts at the 1st of July.
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

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


UPDATE [dimDate]
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).


UPDATE dimDate
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!).