Forum Discussion
Fiscal Year required
Hi,
I would like to have a 4 digit FY column included in my Calendar table so anything belonging to this FY (Apr - Mar) would show 2025.
Then, from Apr 2026 to Mar 2027 the column would show 2026 and so on.
I've tried a combination of LEFT & RIGHT functions on the existing FY field but all I can extract is FY and not the numbers - can anyone help?
Date2 =
VAR FYStartMonth = 4 -- use 4 (Apr), 7 (Jul) or 10 (Oct)
VAR FYStartYear = YEAR(MIN('Cases'[Created On]))
VAR StartDate = DATE(2021,4,1)
VAR Endate = DATE(2026,03,31)
VAR FYCalStart = DATE(FYStartYear, FYStartMonth, 1)
RETURN ADDCOLUMNS(
CALENDAR(StartDate, Endate),
"CalYear", YEAR([Date]),
"CalQtrNumber", Quarter([Date]),
"CalMthNumber", MONTH([Date]),
"DayofMthNumber", DAY([Date]),
"DayofWkNumber", WEEKDAY([Date],2),
"Mth", FORMAT([Date], "mmm"),
"DayofWkName", FORMAT([Date], "ddd"),
"CalQtrName", "Qtr" & QUARTER([Date]),
"YearMthName", FORMAT([Date], "yyyy-mmm"),
"YearMthNum", (YEAR([Date]) * 100) + MONTH([Date]),
"FY", "FY" & YEAR([Date]) - (Month([Date]) < FYStartMonth) & "/" &
YEAR([Date]) + (Month([Date]) >= FYStartMonth),
"FYMonthNo", MOD(MONTH([Date]) - FYStartMonth, 12) +1,
"FY Qtr", "Q" & ROUNDUP(DIVIDE(MOD(MONTH([Date]) - FYStartMonth, 12) +1, 3), 0),
"CurrentMthNo", MONTH(TODAY()))
Hey, ArchStanton ,
drop this to your definition:RETURN ADDCOLUMNS( CALENDAR(StartDate, Endate), "CalYear", YEAR([Date]), "CalQtrNumber", Quarter([Date]), "CalMthNumber", MONTH([Date]), "DayofMthNumber", DAY([Date]), "DayofWkNumber", WEEKDAY([Date],2), "Mth", FORMAT([Date], "mmm"), "DayofWkName", FORMAT([Date], "ddd"), "CalQtrName", "Qtr" & QUARTER([Date]), "YearMthName", FORMAT([Date], "yyyy-mmm"), "YearMthNum", (YEAR([Date]) * 100) + MONTH([Date]), "FY", "FY" & YEAR([Date]) - (Month([Date]) < FYStartMonth) & "/" & YEAR([Date]) + (Month([Date]) >= FYStartMonth), "FYMonthNo", MOD(MONTH([Date]) - FYStartMonth, 12) +1, "FY Qtr", "Q" & ROUNDUP(DIVIDE(MOD(MONTH([Date]) - FYStartMonth, 12) +1, 3), 0), "CurrentMthNo", MONTH(TODAY()), // new column "NewFY", var _year = YEAR([Date]) var _month = MONTH([Date]) var check = IF( _month >= 4, _year, _year-1) return check )
6 Replies
FiscalYear =
IF(MONTH('Date'[Date]) <= 4 ,YEAR('Date'[Date]) + 1,YEAR('Date'[Date]))Hey, ArchStanton ,
drop this to your definition:RETURN ADDCOLUMNS( CALENDAR(StartDate, Endate), "CalYear", YEAR([Date]), "CalQtrNumber", Quarter([Date]), "CalMthNumber", MONTH([Date]), "DayofMthNumber", DAY([Date]), "DayofWkNumber", WEEKDAY([Date],2), "Mth", FORMAT([Date], "mmm"), "DayofWkName", FORMAT([Date], "ddd"), "CalQtrName", "Qtr" & QUARTER([Date]), "YearMthName", FORMAT([Date], "yyyy-mmm"), "YearMthNum", (YEAR([Date]) * 100) + MONTH([Date]), "FY", "FY" & YEAR([Date]) - (Month([Date]) < FYStartMonth) & "/" & YEAR([Date]) + (Month([Date]) >= FYStartMonth), "FYMonthNo", MOD(MONTH([Date]) - FYStartMonth, 12) +1, "FY Qtr", "Q" & ROUNDUP(DIVIDE(MOD(MONTH([Date]) - FYStartMonth, 12) +1, 3), 0), "CurrentMthNo", MONTH(TODAY()), // new column "NewFY", var _year = YEAR([Date]) var _month = MONTH([Date]) var check = IF( _month >= 4, _year, _year-1) return check )This works perfectly, thanks!
- Happy to help.
Hi ArchStanton
To show the fiscal year as the starting year (for example, Apr 2026 – Mar 2027 >> 2026), use this simple DAX formula in your Calendar table:
FiscalYear = VAR FYStartMonth = 4 // 4 = April (set your FY start month) RETURN YEAR([Date]) - IF(MONTH([Date]) < FYStartMonth, 1, 0)HI ArchStanton,
Have you had a chance to review the solution we shared by Thomaslleblanc vojtechsima rohit1991? If the issue persists, feel free to reply so we can help further.
Thank you
