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.
mwegener
Most Valuable Professional
Most Valuable ProfessionalHi Anonymous ,
somehow I misunderstood the requirement, but have a look at the attached file.
Thanks MWegener, solved my problem. Just a little modification, to force the first day of the Fiscal year to be the start of the first fiscal week.
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))