DAX Fiscal Year

If you have a company that has a fiscal year different from the calendar year, you can add an extra column to the DimCalendar table for the Fiscal Year.

For example:

Fiscal Year = 
    VAR fy = IF(DimCalendar[Month Number] > 3, DimCalendar[Year] + 1, DimCalendar[Year])
    RETURN fy

For medicel, we have also implemented a much more complex function for a 4-4-5 reporting month logic:

ReportingMonthHalma =



VAR FiscalStartDate = DATE(YEAR('DimCalendar'[Date]) - IF(MONTH('DimCalendar'[Date]) < 4, 1, 0), 4, 1)  -- Adjust to your fixed fiscal start (e.g., Feb 1)



VAR DayOfWeek = WEEKDAY(FiscalStartDate, 1) //SUNDAY = 1, SATURDAY = 7

VAR NoDaysToPreviousSaturday = - DayOfWeek

VAR StartSaturday = FiscalStartDate - DayOfWeek



VAR m1end = StartSaturday + 4 * 7

VAR m2end = m1end + 4 * 7

VAR m3end = m2end + 5 * 7

VAR m4end = m3end + 4 * 7

VAR m5end = m4end + 4 * 7

VAR m6end = DATE(YEAR(FiscalStartDate), 9, 30)

VAR m7end = m4end + 5 * 7 + 4 * 7

VAR m8end = m7end + 4 * 7

VAR m9end = m8end + 5 * 7

VAR m10end = m9end + 4 * 7

VAR m11end = m10end + 4 * 7

VAR m12end = DATE(YEAR(FiscalStartDate) + 1, 3, 31)





VAR Month =

    SWITCH(TRUE(),

    [Date] <= m1end, 1,

    [Date] <= m2end, 2,

    [Date] <= m1end, 3,

    [Date] <= m1end, 4,

    [Date] <= m1end, 5,

    [Date] <= m1end, 6,

    [Date] <= m1end, 7,

    [Date] <= m1end, 8,

    [Date] <= m1end, 9,

    [Date] <= m1end, 10,

    [Date] <= m1end, 11,

    [Date] <= m1end, 12,

    12)



RETURN

    Month