cancel
Showing results for
Search instead for
Did you mean:
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
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

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

#### 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!

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