Problems with week of month

[RicardoFW]

Hi everyone,

I have a problem trying to get the correct week number of the month. In some months the firts day of month starts on Saturday or Sunday and the function WeekNumber recognize it as the fisrt week of month and the second week starts on Monday. I need the week starts on Monday.

For example:

• 1/05/2021 - 02/05/2021 ==> Week 1
• 3/05/2021 - 09/05/021 ==> Week 2

Now I using this formula to get the week number of month:

WeekNum = WEEKNUM('Date'[Date],21)

StartOfMonth = WEEKNUM(STARTOFMONTH('Date'[Date]),21)

WeekOfMonth = "Week " & 1 + 'Date'[WeekNum] - 'Date'[StartOfMonth]

Thanks for the help

[Anonymous]

DEFINE TABLE
Dates =
    SELECTCOLUMNS(
        CALENDAR(
            date(2020, 6, 1),
            date(2020, 8, 1) - 1
        ),
        "@Date", [Date],
        "@DayNumber",
            // 2 => Monday is 1
            WEEKDAY( [Date], 2 ),
        "@DayName",
            FORMAT( [Date], "ddd" )
    )
EVALUATE
var DatesWithPartialSolution =
    ADDCOLUMNS(
        Dates,
        "@MondaysBackCount",
            var CurrentDate = [@Date]
            var MonthStartDate = 
                EOMONTH( CurrentDate, -1 ) + 1
            return
            COUNTROWS(
                FILTER(
                    Dates,
                    MonthStartDate <= [@Date]
                    &&
                    [@Date] <= CurrentDate
                    &&
                    [@DayNumber] = 1
                )
            )
    )
return
    ADDCOLUMNS(
        DatesWithPartialSolution,
        "@Final",
            // find the latest number
            // that's not blank
            var CurrentDate = [@Date]
            return
            MAXX(
                TOPN(1,
                    FILTER(
                        DatesWithPartialSolution,
                        [@Date] <= CurrentDate
                        &&
                        NOT( ISBLANK( [@MondaysBackCount] ) )
                    ),
                    [@Date],
                    DESC
                ),
                [@MondaysBackCount]
            )
    )

Run the above in DAX Studio or any Power BI client to see what it does. The @Final column stores the number of the week according to your requirement.

[Anonymous]

So you want the first week of every month to start on the first Monday of the month? But then this means that some days in other months will belong to weeks of the previous months. Do you realize this? From this stems the fact that weeks will not divide months evenly and they will cross months' boundaries.