cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdawidow2
Frequent Visitor

Custom Fiscal Week Calculation - Calculated Column Preferred

Hi guys, 

 

I have a custom Date Table that takes into account my Fiscal Year ( Feb - Starting FY month).  It also has a fiscal week calculation.  

 

But I need to take it a step further, and essentially make sure each week has full length period (Monday -Sunday).  

 

So for example, where a fiscal week begins on a day that does not equal "Monday" such as February 1st 2022 (Tuesday) it needs to be pushed to the next week.  

 

So for cases such as above, where the full week is not recognized from Monday To Sunday - it needs to be rolled up into the next week until its showing only 1 week at a time.

 

Is this possible in Power Query ? 

1 REPLY 1
amitchandak
Super User
Super User

@cdawidow2 , check the dax solution

 

FY Year =  if(month([Date]) <2, YEAR([Date]) -1, YEAR([Date]) )

FY Start=  if(month([Date]) <2, Date(YEAR([Date]) -1,2,1) , date(YEAR([Date]),2,1)  )

 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

 

FY Week Start Year = minx(filter('Date',[FY Year] =earlier([FY Year]) && [Week Start date] > [FY Start]  ),[Week Start date])

 

FY Week = QUOTIENT(DATEDIFF([FY Week Start Year ],'Date'[Start of Week]),[Date],DAY),7)+1

 

 



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors