Forum Discussion
Creating a fiscal week column
MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.
It looks like you missed the if statement.
MrMarshall Just want to be clear on this - WeekNum column has Calendar Year WeekNumber isn't it ?
FiscalWeekNum will be the weeknumber starting from April to End of March. In that case how come, Calendar WeekNum 1 has FiscalWeekNum 14.
Thx for anser PattemManohar,
Yes, WeekNum is calendar Year Weeknumber.
The financial year is starting in October, hence on Week 40.
There for, WeekNum 40 should be Fiscal year 1.
And if we continue to count, Weeknum 52 should be Fiscal year 13, and Weeknum 1 should there for be 14.
MrMarshall Might be something like this you are looking for..
FiscalWeekNum = VAR _Step1 = IF(Test51FiscalWeekNum[WeekNum]<40,Test51FiscalWeekNum[WeekNum]+13,0) RETURN IF(_Step1<>0,_Step1,RANKX(FILTER(Test51FiscalWeekNum,Test51FiscalWeekNum[WeekNum]>=40),Test51FiscalWeekNum[WeekNum],,ASC))
Hi! Thanks for the reply PattemManohar,
It almost worked, but the Weeknumbers above 40 is going nuts:
WeekNo FiscalWeekNum 38 51 39 52 40 1 41 218 42 435 43 652 44 869 45 1086 MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.
It looks like you missed the if statement.