Forum Discussion

jsamstad's avatar
jsamstad
Frequent Visitor
9 months ago
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 fiscal year) so it makes adjusting the time confusing. 

 

Thanks in advance!

 

General Comment
  • jsamstad's avatar
    jsamstad
    9 months ago

    Looks like great upgrade! We are running the July version so it is not available.

7 Replies

  • mh2587's avatar
    mh2587
    Super User
    9 months ago
    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]))
)
  • jsamstad's avatar
    jsamstad
    Frequent Visitor
    9 months ago

    Thank you for helping Muhammad! I'm getting different results. I don't see anything populating for the Fiscal Day Offset, the Fiscal Month Offset is ahead by 1, and the Fiscal Week Offset is ahead by 7.

     

     

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    9 months ago

    Hi jsamstad,

    Checking in to see if your issue has been resolved. let us know if you still need any assistance.

     

    Thank you.