FISCAL QUARTER WEEK CALCULATIONS - CONFUSED

cdawidow2 — Fri, 13 May 2022 13:25:36 GMT

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?

Re: FISCAL QUARTER WEEK CALCULATIONS - CONFUSED

amitchandak — Fri, 13 May 2022 14:03:17 GMT

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

topic Re: FISCAL QUARTER WEEK CALCULATIONS - CONFUSED in DAX Commands and Tips

FISCAL QUARTER WEEK CALCULATIONS - CONFUSED

Re: FISCAL QUARTER WEEK CALCULATIONS - CONFUSED