I do not quite understand why you think it is an issue since in Gregorian calendar a year has either 365 or 366 days which - depending on week start setting - yields 52 or 53.
Nevertheless you may use this formula instead:
although the 53rd week will effectively become 1st. You could modify your [purchase date] field to backward those 53rd weeks to the 52nd:
IF DATEPART('week',[purchase_date]) = 53 THEN DATEADD('week', -1, [purchase_date]) ELSE [purchase_date] END
The 53 Week "issue" comes from the how Dates are configured in Tableau.
When you choose "Week Number" Datetrunc from the Dropdown, what Tableau is showing is the Week numbers according to the Default Week Start Day property.
If you haven't changed the settings, Sunday, is the default Start week. So Tableau is essentially showing you Weeks, by the number of Sundays in the year.
This behavior is expected, and follows most business logic. If you want to look at "Weeks" in terms of Monday - Sunday, you need to change the property accordingly. However this does not necessarily mean you will show 52 Weeks everytime. For the same reasons Łukasz Majewski described. Additionally, the Gregorian calendar doesn't break down to 52 Weeks exactly (When looking a 7 day intervals)
But ff you want to look at Weeks in Terms of 7 day intervals starting from January 1st. Then you could use a formula like so
FLOOR((DATEDIFF( 'day', DATE( STR(YEAR([Purchase Date])) + '/01/01'), [Purchase Date] ) ) / 7) + 1
This will allows result in "53 Weeks" but it is relative to a base Date of January 1st, and counts in 7 Day Increments.
Just depends on what you are after.
I would say:
INT(DATEDIFF( 'day', DATETRUNC('year', [Purchase Date]), [Purchase Date] ) / 7) + 1
no need to cast between string and date
Haha! Looks like I couldn't see the Forest for the Trees!
Don't know where my mind was at on the one lol