Forum Discussion
Solved
Creating a fiscal week column
Trying to create a Fiscal week column from WeekNum in my Calendar table. I got the WeekNum done already. Fiscal year starting on Week 40, so I am trying to create something like the one below: I w...
MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.
It looks like you missed the if statement.
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!Weird! Glad you got it though!!
When in doubt, add blank lines and recompile...