Custom Fiscal Week Calculation - Calculated Column Preferred

cdawidow2 — Mon, 25 Apr 2022 13:19:40 GMT

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 ?

Re: Custom Fiscal Week Calculation - Calculated Column Preferred

amitchandak — Mon, 25 Apr 2022 16:35:13 GMT

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

topic Custom Fiscal Week Calculation - Calculated Column Preferred in DAX Commands and Tips

Custom Fiscal Week Calculation - Calculated Column Preferred

Re: Custom Fiscal Week Calculation - Calculated Column Preferred