Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Datagulf
Responsive Resident
Responsive Resident

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

Datagulf
Responsive Resident
Responsive Resident

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

Datagulf
Responsive Resident
Responsive Resident

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Users online (1,071)