Forum Discussion
Date table with fiscal week numbers
Hi Can i get some advice on how to write a date table with fiscal week number please . our company fiscal week starts on the first week of april and ends on the last week of march the following ...
Hi Anonymous ,
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.
This will mark Fiscal Week 53 if April 1st is not the starting day of Fiscal Week 1. You need to isolate the Month Number (to test if it is March (3)).
Fiscal Week =
VAR __fw = [Week Number] - 14 + 1
RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))
Update:
I found out that if the calendar year starts on Sunday the above would have the first day of the new fiscal year falling in the last week of the previous fiscal year. So, I came up with this:
Fiscal Week =
VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, 13, 14) + 1
RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))
Update 2024-10-10:
I found out further anomaly where if the year is a Leap Year and it starts on Sunday, the fiscal week would be wrong in some cases.
Fiscal Week =
VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),14,13),14) + 1
RETURN IF(__fw<=0,IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),53+__fw,52+__fw),IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))