Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Anonymous
Not applicable

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.