cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrMarshall
Helper II
Helper II

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

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

 

It looks like you missed the if statement.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

14 REPLIES 14
ChandeepChhabra
Impactful Individual
Impactful Individual

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

Hi Chandeep,

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

 

Attached an example.

 

EGoodman_0-1632217010508.png

 

 

 

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.

PattemManohar
Community Champion
Community Champion

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

image.png





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

Proud to be a PBI Community Champion




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

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

 

It looks like you missed the if statement.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.