Forum Discussion

cheid1977's avatar
cheid1977
Icon for Advocate I rankAdvocate I
9 years ago
Solved

How to show week number per month?

I need to show how much volume shipped the first, second, third, and fourth week of each month. How can you assign a number (1-4 or 5) to each week of the month?  Thanks.

Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi cheid1977,

     

    Please take a try with the formula below in a calculated column:

    weekinmonth = 1 + WEEKNUM ( 'Calenda'[Date] )-WEEKNUM( STARTOFMONTH ('Calenda'[Date]))

    This formula would work with the month level, which should be no calculated errors.

     

    Please reply back if you need any further assistance on this topic.

    Regards

     

12 Replies

  • v-micsh-msft's avatar
    v-micsh-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi cheid1977,

     

    Please take a try with the formula below in a calculated column:

    weekinmonth = 1 + WEEKNUM ( 'Calenda'[Date] )-WEEKNUM( STARTOFMONTH ('Calenda'[Date]))

    This formula would work with the month level, which should be no calculated errors.

     

    Please reply back if you need any further assistance on this topic.

    Regards

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the response but I have a problem I get Weekmonth in some cases from 1 to 6 . I was Expecting 1to 5 but i get even more possible values. Any idea?

    • leahYan's avatar
      leahYan
      Icon for Advocate II rankAdvocate II
      6 years ago

      This is a good solution, but it only works if all the dates are unique.

      In some cases, you would use a date column like invoiced date, start date, etc

       I hope this code would help:

      Week Num =
 WEEKNUM ( 'Your table'[Your Date column] )-('Your table'[Your Date column].[MonthNo]-1)*4
  • ahmhekal's avatar
    ahmhekal
    New Member
    5 years ago
    I used this formula :
     
    Week = (DAY('Calendar'[Date])+2.5) /7
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      This solved the problem.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    i need add to my date hierarchy the number of month. i dont want show the name month( jan, feb, etc) 
    , i want show the number month (1,2,3). Can you help me ?

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Hard to say without your data or some sample data. I think the right way would be to add a custom column to your Date table.

     

     

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    OK, I didn't fully test this, but the general concept should work, you may have to tweak.

     

    These are all columns:

     

    WeekN = WEEKNUM([Date])

MonthN = MONTH([Date]) 

Column 9 = IF([MonthN] = 1,1,([MonthN]-1)*4)

Column 5 = IF([Column 9]=1,1,[WeekN] - [Column 9] - 1)
  • jkaluki254's avatar
    jkaluki254
    New Member
    3 years ago

    Did you ever get a solution for this, currently facing the same challenge........:(