Forum Discussion
Adding One Date to One Fiscal Year
Dear Community,
My company's fiscal year starts from Nov 1st each year and ends with Oct 31st as the last date every year.
Currently, For each fiscal year, I have created a date table with dates, month number & year and a formula for its ficsal year column:
Fiscal Year =
Var _FiscalMonthStart = 11
Return
IF(
Datetable[MonthNumber]>= _FiscalMonthStart
Datetable[Year] +1,
Datetable[Year]
)
I am working on a special request from my boss at the moment:
"To add Nov 1st, 2024 as the last date of Fiscal 2024 WITHOUT impacting all other fiscal year"
In other words, ONLY Fiscal 2024 starts from Nov 1st, 2023 till Nov 1st, 2024. Then Fiscal 2025 starts from Nov 2nd.
While ALL the years before Starts from Nov 1st till Oct 31st.
May I know if anyone can help to address this special request?
Thank you very much!
Hey ljx0648 ,
To accommodate your boss's request to add November 1, 2024, as the last date of Fiscal Year 2024 without affecting other fiscal years, you can try modify your DAX formula by adding a conditional statement that specifically checks for dates within the Fiscal Year 2024 range. Here’s a modified version of your formula:
Fiscal Year =
VAR _FiscalMonthStart = 11
RETURN
IF (
(Datetable[Year] = 2024 && Datetable[MonthNumber] = 11 && Datetable[Day] = 1), // Special case for November 1, 2024
2024,
IF (
Datetable[MonthNumber] >= _FiscalMonthStart,
Datetable[Year] + 1,
Datetable[Year]
)
)
Let me know if works!
2 Replies
Hey ljx0648 ,
To accommodate your boss's request to add November 1, 2024, as the last date of Fiscal Year 2024 without affecting other fiscal years, you can try modify your DAX formula by adding a conditional statement that specifically checks for dates within the Fiscal Year 2024 range. Here’s a modified version of your formula:
Fiscal Year =
VAR _FiscalMonthStart = 11
RETURN
IF (
(Datetable[Year] = 2024 && Datetable[MonthNumber] = 11 && Datetable[Day] = 1), // Special case for November 1, 2024
2024,
IF (
Datetable[MonthNumber] >= _FiscalMonthStart,
Datetable[Year] + 1,
Datetable[Year]
)
)
Let me know if works!Worked like a charm!
Much appreciated!
