cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrMarshall Regular Visitor
Regular Visitor

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

WeekNumFiscal WeekNum
114
215
316
417
518
619
720
821
922
1023
1124
1225
1326
1427
1528
1629
1730
1831
1932
2033
2134
2235
2336
2437
2538
2639
2740
2841
2942
3043
3144
3245
3346
3447
3548
3649
3750
3851
3952
401
412
423
434
445
456
467
478
489
4910
5011
5112
5213

 

Any ideas ?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


9 REPLIES 9
Super User
Super User

Re: Creating a fiscal week column

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Creating a fiscal week column

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





MrMarshall Regular Visitor
Regular Visitor

Re: Creating a fiscal week column

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.

Screenshot_173.png

MrMarshall Regular Visitor
Regular Visitor

Re: Creating a fiscal week column

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. 

Super User
Super User

Re: Creating a fiscal week column

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

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





MrMarshall Regular Visitor
Regular Visitor

Re: Creating a fiscal week column

Hi! Thanks for the reply @PattemManohar,

 

It almost worked, but the Weeknumbers above 40 is going nuts:

WeekNoFiscalWeekNum
3851
3952
401
41218
42435
43652
44869
451086
Super User
Super User

Re: 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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MrMarshall Regular Visitor
Regular Visitor

Re: Creating a fiscal week column

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 Smiley Happy 

Thanks!

Super User
Super User

Re: Creating a fiscal week column

Weird! Glad you got it though!!

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 104 members 1,672 guests
Please welcome our newest community members: