Forum Discussion
Function To Calculate Fiscal Week
Hi all, I want to calculate the number of weeks based on the date of sale, I used the formula: week = WEEKNUM(Scorecard[SalesDate],2) This works very well but not for what I need, Due to work ...
Saw some incredibly complex solutions, a couple a bit simpler. Wanted to try and provide a simpler approach for those who may be struggling.
The solution assumes you've created a DateDim table (how-to), and, that the table has the fields referenced, such as Year, Month, Day, etc. All date references are CY unless specified Fiscal. In this example, the fiscal calendar begins on April 1st (4/1/####).
if #date([Year],[Month],[Day]) >= (#date([Year],4,1))
then
([Week of Year] - ((Date.WeekOfYear(#date([Year],4,1))))) +1
else
(Date.WeekOfYear(#date([Year],12,31)) - Date.WeekOfYear(#date([Year],4,1))) + [Week of Year]
The initial IF statement ensures the condition of a FY starting mid-week is properly handled.