Forum Discussion
ArchStanton
Power Participant
10 months agoFiscal 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 a...
- 10 months ago
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 )
vojtechsima
Super User
10 months agoHey, 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
)- ArchStanton10 months ago
Power Participant
This works perfectly, thanks!
- vojtechsima10 months ago
Super User
Happy to help.