March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 ?
Solved! Go to Solution.
@MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.
It looks like you missed the if statement.
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/
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
Hi Chandeep,
Quick question is there a way to get the weeks to continously count from YOA?
Attached an example.
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!
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.
@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.
Proud to be a PBI Community Champion
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))
Proud to be a PBI Community Champion
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!
Is it because of the 0,52? I am assuming that one of your computers is recognizing the comma as a decimal and it may be causing the issue.
Weird! Glad you got it though!!
When in doubt, add blank lines and recompile...
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |