Create Column on Date table that shows week no 1-4/5 and start from one again in a new month

[Datagulf]

I have a date table in years, quarters, months, weeks, and days. For the weeks, I have the code as below. 

Fiscal Week = 
--Inputs--
VAR WeekStartsOn = "Sun"
VAR FiscalStartMonth = 4

--Calculation--
VAR FiscalFirstDay = 
    IF(
        MONTH('Calendar'[Date]) < FiscalStartMonth,
        DATE(
            YEAR('Calendar'[Date])-1,
            FiscalStartMonth,
            1
        ),
        DATE(
            YEAR('Calendar'[Date]),
            FiscalStartMonth,
            1
        )
    )
VAR FilteredTableCount = 
    COUNTROWS(
        FILTER(
            SELECTCOLUMNS(
                GENERATESERIES(
                    FiscalFirstDay,
                    'Calendar'[Date]
                ),
                "Dates",
                [Value]
            ),
        FORMAT([Dates],"ddd") = WeekStartsOn
        )
    )
VAR WeekNos = 
    IF(
        FORMAT(FiscalFirstDay,"ddd") <> WeekStartsOn,
        FilteredTableCount + 1,
        FilteredTableCount
    )
RETURN
    "Week " & WeekNos

However, for this code, it shows weeks in a continuous form from week 1 to week 52. However, I am trying to tweak it to show, the weeks week one to four / five and begin the next month on week one again. 
Remember a new month, does not have to be the beginning of a new week. So, how does the first Sunday become the beginning of week number 1 of that new month. 

You don't have to use the code above. As long as it works and I have week numbers with the first Sunday being the beginning of that month.  Thanks in advance

[Fowmy]

@Datagulf 

Can you some sample data with expected results? You may attach an Excel file link after saving in One Drive .

[Datagulf]

Hey @Fowmy , this is how the months look like.

When drilled down to weeks it looks like this. 

So all I want is for weeks to label 1 to 4 weeks and start from 1 in the next month. 

For Data, you can use any that has a date column and has values that can be put in a bar and drill down.

it's only about the weeks' Date column

[Fowmy]

@Datagulf 

If you want to start Week 1 on the first Sunday then how do you name the days prior to that in a month? 

If the 3rd is Sunday and you need to set Week1 from that point onwards, what do call 1st and 2nd?

Add the following as a new column but I did not consider it?Sunday as begining:

Week = 
VAR __DAY =  DAY(Dates[Date dd])
    RETURN
    SWITCH(
        TRUE(),
        __DAY >= 1 && __DAY <= 7, "Week 1",
        __DAY >= 8 && __DAY <= 14, "Week 2",
        __DAY >= 15 && __DAY <= 21, "Week 3",
        __DAY >= 22 && __DAY <= 28, "Week 4",
        "Week 5"
    )
    

[Datagulf]

Hey  @Fowmy, I believe this is on the way to the solution. However, it is somewhat wrong because there are months where I will have 1 or 2 days in week 5.
However, I don't know if this will help. 
So in my date column, I have a column Weekday/Weekend which labels weekdays as 1 and weekends as 0. How would we start labelling from the first 0(First Sunday) Week1 and the subsequent Sundays week2, week 3, week 4 and again the Dax checks the first 0 in the next month and again week 1? 

it does not matter  a week spilling into a month, as long the first Sunday shows the beginning of the week and all weeks are 7 days except maybe the first and last one of the year.

[v-yiruan-msft · Accepted solution]

Hi @Datagulf ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column to get the YearMonth

YearMonth = 
VALUE (
    CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "mm" ) )
)

2. Create a calculated column to get the workday from 1(Sunday) to 7(Saturday)

Workday = WEEKDAY('Calendar'[Date],1)

3. Create another calculated column to get the week number for per month

Weeknum = 
VAR _countofpermonth =
    CALCULATE (
        COUNT ( 'Calendar'[Workday] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[YearMonth]
                = EARLIER ( 'Calendar'[YearMonth] ) - 1
                && 'Calendar'[Workday] = 1
        )
    )
VAR _tempweeknum =
    CALCULATE (
        SUM ( 'Calendar'[Workday] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[YearMonth] = EARLIER ( 'Calendar'[YearMonth] )
                && 'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] )
                && 'Calendar'[Workday] = 1
        )
    )
RETURN
    IF ( ISBLANK ( _tempweeknum ), _countofpermonth, _tempweeknum )

Best Regards