Forum Discussion

cdawidow2's avatar
cdawidow2
Frequent Visitor
4 years ago

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 ? 

Date
DAX
Help Requested
Time Intelligence

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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