Month, Week, Day Offset Columns

[jsamstad]

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!

[v-saisrao-msft]

Hi @jsamstad,

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

Thank you.

[jsamstad]

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]

Hi @jsamstad,

Thank you @mh2587, for your insights.

Please refer to the documentation below for more details, as it may help resolve your issue:

Implement time-based calculations in Power BI - Power BI | Microsoft Learn

Thank you.

[jsamstad · Accepted solution]

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

[v-saisrao-msft]

Hi @jsamstad,

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

Thank you.

[v-saisrao-msft]

Hi @jsamstad,

Just try updating the version and check if the issue is resolved if not please share sample pbix file and the error message  you got.

Thank you.

[mh2587]

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]))
)