Current Fiscal Week with ISO Calendar

Question

I have implemented a fiscal calendar into my Power BI Data Model for some time intelligence magic. This fiscal calendar has a specific start and end date for each month and is mainly based on ISO 8601 Calendar (Gregorian). However, my current approach is not working anymore for this year, since the filter "Current Fiscal Week" is not working. If I filter on Current Fiscal Week, then I get the second week of January instead of the first week. But why? My code has the following structure:

Calendar =
VAR BaseCalendar =
    CALENDAR (
        DATE ( 2020, 1, 1 ),
        DATE ( 2022, 01, 03 )
    )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR WeekNumber =
            WEEKNUM ( BaseDate )
        VAR Year =
            YEAR ( BaseDate )
        VAR FiscalWeek =
            WEEKNUM (
                BaseDate,
                21
            )
        VAR FiscalYear =
            IF (
                FiscalWeek < 5
                    && WeekNumber > 50,
                Year + 1,
                IF (
                    FiscalWeek > 50
                        && WeekNumber < 5,
                    Year - 1,
                    Year
                )
            )
        RETURN
            ROW (
                "Fiscal Week", FiscalWeek,
                "Current Fiscal Week",
                    IF (
                        AND (
                            FiscalWeek
                                = WEEKNUM (
                                    TODAY (),
                                    2
                                ),
                            FiscalYear
                                = YEAR (
                                    TODAY ()
                                )
                        ),
                        TRUE (),
                        FALSE ()
                    )
            )
    )

sergiom sergiom · Accepted Answer · 2021-01-05T18:22:49

I think that the Current Fiscal week should be found with the same formulas used to compute the FiscalWeek and FiscalYear. I put the variable definitions for the new variables outside of the GENERATE, since they just depend on TODAY()

VAR TodayBaseDate =
    TODAY()
VAR TodayWeekNumber =
    WEEKNUM( TodayBaseDate )
VAR TodayYear =
    YEAR( TodayBaseDate )
VAR TodayFiscalWeek =
    WEEKNUM( TodayBaseDate, 21 )
VAR TodayFiscalYear =
    IF(
        TodayFiscalWeek < 5
            && TodayWeekNumber > 50,
        TodayYear + 1,
        IF( TodayFiscalWeek > 50 && TodayWeekNumber < 5, TodayYear - 1, TodayYear )
    )
VAR BaseCalendar =
    CALENDAR( DATE( 2020, 1, 1 ), DATE( 2022, 01, 03 ) )
RETURN
    GENERATE(
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR WeekNumber =
            WEEKNUM( BaseDate )
        VAR Year =
            YEAR( BaseDate )
        VAR FiscalWeek =
            WEEKNUM( BaseDate, 21 )
        VAR FiscalYear =
            IF(
                FiscalWeek < 5
                    && WeekNumber > 50,
                Year + 1,
                IF( FiscalWeek > 50 && WeekNumber < 5, Year - 1, Year )
            )
        RETURN
            ROW(
                "Week day", FORMAT( [Date], "ddd" ),
                "Today", TODAY(),
                "Fiscal Week", FiscalWeek,
                "Current Fiscal Week",
                    AND( FiscalWeek = TodayFiscalWeek, FiscalYear = TodayFiscalYear )
            )
    )

Current Fiscal Week with ISO Calendar

1 Answers