Forum Discussion

MNM's avatar
MNM
Frequent Visitor
3 years ago

Customize Fiscal Week

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 belo...
Need Help
Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
3 years ago

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's avatar
MNM
Frequent Visitor
3 years ago

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