Calendar Table - Week Number of Month

Calendar Table - Week number of month

update TCalendar 
set = WeekNumberOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, FullDateAlternateKey), 0), FullDateAlternateKey) +1

Power BI - Convert Week Number to Month of the year

Your question is not entirely clear.

Assuming you want to get the

• Month Name from the date of the first day of the week given by the week number
• and the week number is computed as an ISO week number,

Then I would first add a Custom Function to compute the relevant Date:

//fnDateFromISOwn
//compute date of the first day of the week given an ISO weeknumber
//if the Year is not entered, it will default to "this" year
//Year must be a full year: eg 2021 vs 21. The latter will not be interpreted as the year 2021.

(wn as number, optional year as number) =>
let  
    yr = if year = null then Date.Year(DateTime.LocalNow()) else year,
    wn1Start = Date.StartOfWeek(#date(yr,1,1),Day.Monday),
    w1 = if Date.AddDays(wn1Start,3) < #date(yr,1,1) then Date.AddDays(wn1Start,7) else wn1Start

in Date.AddDays(w1, 7*(wn-1))

Then you can add a custom column in your main code:

    #"Added Custom" = Table.AddColumn(#"Previous Step", "Month", each 
        Date.MonthName(fnDateFromISOwn([Week Number], Date.Year([New Status Date]))))

Source

Results

On the other hand,

• if there is a relationship in that the weeknumber always corresponds to the New Status Date,
• and it is still an ISO weeknumber,
• and you still want the month name corresponding to the date at the start of that week

then you can use the simpler formula where you merely compute the previous Monday to the New Status Date, and obtain the MonthName from that date.

Date.MonthName(Date.StartOfWeek([New Status Date],Day.Monday)))

How to get the Week of the month (1:6)

The DatePart function is perfect for this. Using DatePart with the interval set to "weeks" you can find the week of a given date. Then you subtract the number of weeks before the first day of that month (which sets the first day to week = 1).

Function WeekNumOfDate(D As Date) As Integer
    WeekNumOfDate = DatePart("ww", D) - DatePart("ww", DateSerial(Year(D), Month(D), 1)) + 1
End Function

Here is a second version of the function that has the ability to set the FirstDayOfWeek argument:

Function WeekNumOfDate(D As Date, Optional FirstDayOfWeek As VbDayOfWeek = vbSunday) As Integer
    WeekNumOfDate = DatePart("ww", D, FirstDayOfWeek) - DatePart("ww", DateSerial(Year(D), Month(D), 1), FirstDayOfWeek) + 1
End Function

As an example for using FirstDayOfWeek: With FirstDayOfWeek set to vbThursday, the date "Nov 5th, 2021" will return as Week 2, whereas it would by default be counted as Week 1. November 1st to 3rd of 2021 will be week 1, and then 4th to 10th will be week 2.

Week number of the month?

In order to use straight division, the day of month for the date you're looking at needs to be adjusted according to the position (within the week) of the first day of the month. So, if your month happens to start on a Monday (the first day of the week), you can just do division as suggested above. However, if the month starts on a Wednesday, you'll want to add 2 and then do the division. This is all encapsulated in the function below.

from math import ceil

def week_of_month(dt):
    """ Returns the week of the month for the specified date.
    """

    first_day = dt.replace(day=1)

    dom = dt.day
    adjusted_dom = dom + first_day.weekday()

    return int(ceil(adjusted_dom/7.0))

MS SQL week number of month at date

September 30, 2019 is in the 5th week, not the sixth, if the default first day of the week is used. That is specified by the DATEFIRST setting. This affects date calculations like DATEADD and DATEPART(weekday)

If you want to use a different first day, eg Monday, you'll have to change the setting with

SET DATEFIRST 1;

When you make that change, your last query works both for 2019-09-01 and 2019-09-30

set datefirst 1

declare @date datetime = '2019-09-30'

select (day(@date)+datepart(weekday,dateadd(day,1-day(@date),@date))- datepart(weekday,@date))/7+1

This returns 6. For 2019-01-01 it returns 1

Fetch week numbers with date range of each week for a whole year

There are a lot of ways to solve this problem. A brute force approach generates all the dates using a some method -- this uses a recursive CTE -- and then assigns the weeks based on a window function counting the Mondays.

with cte as (
      select datefromparts(2021, 1, 1) as dte
      union all
      select dateadd(day, 1, dte)
      from cte
      where dte < datefromparts(2021, 12, 31)
     )
select weeknum, min(dte), max(dte)
from (select cte.*,
             sum(case when datename(weekday, dte) = 'Monday' then 1 else 0 end) over (order by dte) as weeknum
      from cte
     ) cte
group by weeknum
option (maxrecursion 0);

This is fine for a one-off query, but isn't the most efficient method to generate the weeks. It is, however, a fun illustration of recursive CTEs. That said, you should probably really have a calendar table of some sort that encapsulates this information as a table.

---

Related Topics