cancel
Showing results for
Did you mean:
Helper I

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

1 ACCEPTED SOLUTION
Microsoft

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

7 REPLIES 7
New Member

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 ?

Microsoft

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

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```
Anonymous
Not applicable

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?

Helper IV

This answer was very usefull to me! Thanks

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!