Forum Discussion
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 would include my Dax code if I had some...
| WeekNum | Fiscal WeekNum |
| 1 | 14 |
| 2 | 15 |
| 3 | 16 |
| 4 | 17 |
| 5 | 18 |
| 6 | 19 |
| 7 | 20 |
| 8 | 21 |
| 9 | 22 |
| 10 | 23 |
| 11 | 24 |
| 12 | 25 |
| 13 | 26 |
| 14 | 27 |
| 15 | 28 |
| 16 | 29 |
| 17 | 30 |
| 18 | 31 |
| 19 | 32 |
| 20 | 33 |
| 21 | 34 |
| 22 | 35 |
| 23 | 36 |
| 24 | 37 |
| 25 | 38 |
| 26 | 39 |
| 27 | 40 |
| 28 | 41 |
| 29 | 42 |
| 30 | 43 |
| 31 | 44 |
| 32 | 45 |
| 33 | 46 |
| 34 | 47 |
| 35 | 48 |
| 36 | 49 |
| 37 | 50 |
| 38 | 51 |
| 39 | 52 |
| 40 | 1 |
| 41 | 2 |
| 42 | 3 |
| 43 | 4 |
| 44 | 5 |
| 45 | 6 |
| 46 | 7 |
| 47 | 8 |
| 48 | 9 |
| 49 | 10 |
| 50 | 11 |
| 51 | 12 |
| 52 | 13 |
Any ideas ?
MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.
It looks like you missed the if statement.
14 Replies
I have recently written a blog post to customize the fiscal week. Please check it out here - https://www.goodly.co.in/calculate-fiscal-week-in-power-bi/
- You'll have the option to customize the fiscal year start month.
- And starting day of the week - Eg. mon, tue etc..
Just copy the DAX code and create a new column in your date table and paste it there! It should work fine!
Let me know..thanks
Defintely a more complete answer. You take care of most of the edge cases.
Personal suggestion, I'd make sure localization can't impact results.
In my case, I had to use a lowercase case for the first day of week (working in French) since in English use a capital letter for weekdays.
Great work, thanks.
This is *exactly* what I was looking for, and my fiscal year even starts in April and my fiscal weeks on Mondays, so that was serendipitous! Thank you so much!
A little ugly, might have to account for 53 week years although that wouldn't be the end of the world.
Fiscal Week = VAR __fw = [WeekNum] - 40 + 1 RETURN IF(__fw<=0,52+__fw,__fw)
Thx for answer Greg_Deckler,
I am afraid I didn't get the query to work the way it should have. I get the results below.
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))
