Customize Fiscal Week

[MNM]

Hi!

I am stuck with this scenario. Is it possible to create a week number column wherein the first week consist of 8 days and the 4th or 5th week is 6 days depending on the month? Please see below sample.

Jan 28 to Feb 4 = 1st week

Feb 5 to Feb 11 = 2nd week

Feb 12 to Feb 18 = 3rd week

Feb 19 to feb 24 = 4th week

Feb 25 to Mar 4 = 1st week

Mar 5 to Mar 11 = 2nd week

Mar 12 to 18 = 3rd week

Mar 19 to Mar 25 = 4th week

Mar 26 to 31 = 5th week

an so on...

Thank you in advance.

[Sahir_Maharaj]

Hello @MNM,

You can use the DAX function WEEKNUM to achieve this.

Custom Week Number =
VAR DaysInWeek =
    IF(
        MONTH([Date]) <> MONTH([Date] - 7),
        8,
        IF(
            MONTH([Date]) = 2,
            IF(
                DAY([Date] - WEEKDAY([Date], 1)) < 28,
                8,
                IF(
                    DAY([Date] - WEEKDAY([Date], 1)) > 28,
                    6,
                    7
                )
            ),
            7
        )
    )
RETURN
    WEEKNUM([Date], 2) +
        IF(
            WEEKDAY([Date], 2) = 1,
            0,
            IF(
                WEEKDAY([Date], 2) < 5,
                1,
                IF(
                    WEEKDAY([Date], 2) + DaysInWeek > 7,
                    2,
                    1
                )
            )
        )

Hope this helps. Should you require further assistance, please do not hesitate to reach out.

[MNM]

Hello @Sahir_Maharaj 

I tried applying the dax but its not showing the correct week number.

Custom Week Number =

VAR DaysInWeek =

    IF(

        MONTH('Dates'[Date]) <> MONTH('Dates'[Date] - 7),

        8,

        IF(

            MONTH('Dates'[Date]) = 2,

            IF(

                DAY('Dates'[Date] - WEEKDAY('Dates'[Date],1)) < 28,

                8,

                IF(

                    DAY('Dates'[Date] - WEEKDAY('Dates'[Date],1)) > 28,

                    6,

                    7

                )

            ),

            7

        )

    )

RETURN

    WEEKNUM('Dates'[Date],2) +

        IF(

            WEEKDAY('Dates'[Date], 2) = 1,

            0,

            IF(

                WEEKDAY('Dates'[Date], 2) < 5,

                1,

                IF(

                    WEEKDAY('Dates'[Date],2) + DaysInWeek > 7,

                    2,

                    1

                )

            )

        )

           

Thank you in advance