cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Datagulf
Helper V
Helper V

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

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

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

yingyinr_1-1631691783867.png

Best Regards

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

View solution in original post

5 REPLIES 5
Fowmy
Super User
Super User

@Datagulf 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Datagulf_0-1630499843476.png

When drilled down to weeks it looks like this. 

Datagulf_1-1630499985292.png

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

@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"
    )
    

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

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 )

yingyinr_1-1631691783867.png

Best Regards

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.