Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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
@Datagulf
Can you some sample data with expected results? You may attach an Excel file link after saving in One Drive .
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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"
)
⭕ 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 )
Best Regards
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
73 | |
49 |
User | Count |
---|---|
142 | |
139 | |
110 | |
69 | |
55 |