cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Community Support

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

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.
5 REPLIES 5
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
Responsive Resident

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

Super User

@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
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.

Community Support

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

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors