Creating a fiscal week column

[MrMarshall]

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 ?

[ChandeepChhabra]

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

[Anonymous]

Hi Chandeep,

Quick question is there a way to get the weeks to continously count from YOA?

Attached an example.

[SQLSessions]

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!

[esylvestre]

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.

[PattemManohar]

@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.

[MrMarshall]

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. 

[PattemManohar]

@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))

[MrMarshall]

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

[Greg_Deckler · Accepted solution]

@MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.

It looks like you missed the if statement.

[MrMarshall]

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!

[dhall001]

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.

[Greg_Deckler]

Weird! Glad you got it though!!

When in doubt, add blank lines and recompile...

[Greg_Deckler]

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)

[MrMarshall]

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.