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.
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.
That worked Greg!
There must be some culture error on my Power BI.
I changed:
VAR __fw = [WeekNum] - 40 + 1 RETURN IF( __fw <= 0,52 + __fw , __fw )
To:
VAR __fw = [WeekNum] - 40 + 1 RETURN IF( __fw <= 0 , 52 + __fw , __fw )
and it works after the change of spaces :)
Thanks!