Forum Discussion
Solved
Month, Week, Day Offset Columns
Hello! I'm trying to create Month, Week, Day offset columns for time intelligence purposes. I've attached our fiscal 445 calendar. We are currently in FY26 and period 9 (2/2 is the start of the f...
Looks like great upgrade! We are running the July version so it is not available.
Fiscal Day Offset = //Try these calculated columns for 445 fiscal calendar (4-4-5 pattern)
DATEDIFF(
CALCULATE(
MIN('Calendar'[Date]),
FILTER('Calendar', 'Calendar'[FiscalYearID] = EARLIER('Calendar'[FiscalYearID]) &&
'Calendar'[FiscalMonthNumber] = 1 &&
'Calendar'[FiscalWeekOfMonthNumber] = 1)
),
'Calendar'[Date],
DAY
)
--------------------------------------------------
Fiscal Week Offset =
'Calendar'[FiscalWeekOfYear] -
CALCULATE(
MIN('Calendar'[FiscalWeekOfYear]),
FILTER('Calendar', 'Calendar'[FiscalYearID] = EARLIER('Calendar'[FiscalYearID]))
)
---------------------------------------------------
Fiscal Month Offset =
'Calendar'[FiscalMonthNumber] -
CALCULATE(
MIN('Calendar'[FiscalMonthNumber]),
FILTER('Calendar', 'Calendar'[FiscalYearID] = EARLIER('Calendar'[FiscalYearID]))
)