Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
91 | |
86 | |
76 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |