CODEWITHSUNDEEP
powerbidax
Aaron
Aaron

Reputation: 331

Current Fiscal Week with ISO Calendar

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 ()
                    )
            )
    )

Upvotes: 0

Views: 408

Answers (1)

sergiom
sergiom

Reputation: 4887

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 )
            )
    )

Upvotes: 1

Related Questions