FISCAL QUARTER WEEK CALCULATIONS - CONFUSED

[cdawidow2]

Hi guys,  I developed a calculated column to group dates within a Fiscal Quarter into their respective Week Groupings within the fiscal quarter.  

The measure works perfectly for FQ1,FQ2,FQ3 but not for FQ4 which I believe is because the calculation is confused by my fiscal periods ( Fiscal Year Begins in Feb) 

Here is my measure : 

VAR Q = 'DATE'[FiscalQuarter]

VAR Y = 'DATE'[FiscalYear]

VAR FYMONTH = 'DATE'[FiscalMonthNum]

VAR MONTH1 = FORMAT('DATE'[Date],"MMM")

VAR WEEKNUM1 =

RANKX(FILTER('DATE','DATE'[FiscalQuarter] = Q &&

'DATE'[FiscalYear] = Y

),

WEEKNUM('DATE'[Date],2),,ASC,Dense)

VAR WEEKNUMBERS = IF('DATE'[QUARTER START DAY] <> "Mon" && WEEKNUM1 = 1,1,IF('DATE'[QUARTER START DAY] <> "Mon",WEEKNUM1-1,WEEKNUM1))

RETURN WEEKNUM1

Here are my skewed reuslts for Q4 : As you can see FQ4 should begin in November but the weeks start ascending in January, then November and finally December but the tru order should be November, December and then January.  Any idea how to fix my formula to take into account my fiscal period?

[amitchandak · Accepted solution]

@cdawidow2 , This should be you FY Qtr Week

New column =
var _st = [QUARTER START DAY]
Var _mon = _st -weekday(_st,2)+1
return
Quotient(datediff(_mon,[Date],day),7)+1