How to add the start of month on my weekly date column

[Anonymous]

Hi

Am trying to create a column in which is gives me the week no. of each month ( Every 7 days = 1 Week).

I successfuly created it, but am now facing an issue which the next month it continues counting and does not start again from week 1 as a new month.

I tried to use the "startofmonth" but couldn't make work.

Here's my code:

Column = " Week " & 1 + INT((INT('Calendar'[Date] - DATE(YEAR('Calendar'[Date]),1,1)))/7) & " - " & FORMAT('Calendar'[Date], "MMM")

And here's a screenshot of the column:

[johnt75]

Column =
var weekNum = QUOTIENT( 'Calendar'[Day of month], 7 ) + 1
return "Week " & weekNum

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous,

According to your description, here's my solution.

Column =
" Week "
    & 1
        + INT (
            (
                INT (
                    'Calendar'[Date]
                        - DATE ( YEAR ( 'Calendar'[Date] ), MONTH ( 'Calendar'[Date] ), 1 )
                )
            ) / 7
        ) & " - "
    & FORMAT ( 'Calendar'[Date], "MMM" )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Not very clear

Usually, Start of month is

start of month  = eomonth([Date],-1)+1

Also check

Start of Week = [Date] -[WeekDay]+1 //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

[Anonymous]

Am not looking for the start of month itself, just in how to put it in my code:

Column = " Week " & 1 + INT((INT('Calendar'[Date] - DATE(YEAR('Calendar'[Date]),1,1)))/7) & " - " & FORMAT('Calendar'[Date], "MMM")

My code now shows the week no. like this:

January 1-7 = Week 1

January 8-14 = Week 2

January 15-21 = week 3

etc

but when it comes to Feb for example it conitnues like:

Feb 1-7 = Week 6

Feb 8-14 = Week 7

etc

I want it to be like this

Jan 21-28 = Week 4

Jan 29-31 = Week 5

then restart

Feb 1-7 = Week 1

Feb 8-14 = Week 2

etc