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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.