Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
10 months ago
Solved

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 a...
  • vojtechsima's avatar
    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
        )