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

 

Need Help

2 Replies

  • 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