Forum Discussion
cdawidow2
4 years agoFrequent Visitor
FISCAL QUARTER WEEK CALCULATIONS - CONFUSED
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?
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
1 Reply
- amitchandak
Super User
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