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
Hey Guys,
Good day. I would like to ask for some help, because I would like to create a column for WEEK Number for each dates, but I am getting incorrect values, e.g : March 26, 2017 to March 31, 2017, I am expecting to get WEEK 5 for those dates, but I am getting WEEK 1 which INCORRECT.
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertYearMonth = Table.AddColumn(InsertCalendarMonth, "YearMonthCalendar", each [Year] * 100 + [MonthOfYear]),
InsertCalendarQtr = Table.AddColumn(InsertYearMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
Source = Query1(#date(2017, 1, 1), #date(2017, 12, 31), null),
#"Inserted Week of Month" = Table.AddColumn(Source, "WeekOfMonth", each Date.WeekOfMonth([WeekEnding]), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Week of Month", "WeekNameMonth", each if [WeekOfMonth] = 1 then "Week 1"
else if [WeekOfMonth] = 2 then "Week 2"
else if [WeekOfMonth] = 3 then "Week 3"
else "Week 4"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"WeekNameMonth", "Weeks"}, {"DayOfWeekName", "Day of the Week"}})
SEE attached screenshot
THANK YOU!!!
Solved! Go to Solution.
@Anonymous,
In your custom column WeekOfMonth, why do you use WeekEnding argument in Date.WeekOfMonth function, you could use Date on that fucntion. I have tested it on my local environment, please refer to the screenshot below.
Regards,
Charlie Liao
Hey Guys,
Good day. I would like to ask for some help, because I would like to create a column for WEEK Number for each dates, but I am getting incorrect values, e.g : March 26, 2017 to March 31, 2017, I am expecting to get WEEK 5 for those dates, but I am getting WEEK 1 which INCORRECT.
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertYearMonth = Table.AddColumn(InsertCalendarMonth, "YearMonthCalendar", each [Year] * 100 + [MonthOfYear]),
InsertCalendarQtr = Table.AddColumn(InsertYearMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
Source = Query1(#date(2017, 1, 1), #date(2017, 12, 31), null),
#"Inserted Week of Month" = Table.AddColumn(Source, "WeekOfMonth", each Date.WeekOfMonth([WeekEnding]), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Week of Month", "WeekNameMonth", each if [WeekOfMonth] = 1 then "Week 1"
else if [WeekOfMonth] = 2 then "Week 2"
else if [WeekOfMonth] = 3 then "Week 3"
else "Week 4"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"WeekNameMonth", "Weeks"}, {"DayOfWeekName", "Day of the Week"}})
SEE attached screenshot
THANK YOU!!!
hi,
just sharing of what works for me on this leap year (total wk 53 ) i found somewhere on the net last time :
WW =
VAR wk = WEEKNUM('Calendar Relative'[Date], 2)
VAR yr = YEAR('Calendar Relative'[Date])
VAR isowk = WEEKNUM('Calendar Relative'[Date], 21)
return
IF (wk > 50 && isowk < 5, FORMAT(isowk,"00") & " " & yr+1,
IF (wk < 5 && isowk >50 , FORMAT(isowk,"00") & " " & yr-1, FORMAT(isowk,"00") & " " & yr))
In the Query Editor-The week number for the first week of the year is not accurate. I am using the date.weekofyear (by copying the transaction date column and transforming it into the week of the date).
Here is what I'm seeing: (Sorry i cant figure out to attach a picture)
Transaction Date Week of Year of Date Start of Week End of Week
12/29/2017 52 12/24/2017 12/30/2017
12/30/2017 52 12/24/2017 12/30/2017
12/31/2017 53 12/31/17 1/6/18
1/1/2018 1 12/31/17 1/6/18
1/2/2018 1 12/31/17 1/6/18
As you can see, all of the tranasctions are marked with the correct week# EXCEPT the transactions on 12/31/17. It is putting them in week 53, and that is the only date in week 53. The start and end of week columns are being calculated correctly, but it is pulling the wrong week number.
Did you solve the week number 53 isses? I'm getting the same problem.
53 seems to build up each year, 1 more day in week 53 for every year until 7 then drops back.
I don't have the knowledge to fix this correctly but instead I've better approximated based on day number...
Find the running day number, add 6, then divide by 7 and round this whole lot down using INT. This only gets a single week 53 which I'm prepared to then do IF 53 make the week number 52:
@Anonymous,
In your custom column WeekOfMonth, why do you use WeekEnding argument in Date.WeekOfMonth function, you could use Date on that fucntion. I have tested it on my local environment, please refer to the screenshot below.
Regards,
Charlie Liao
Thank you so much!
Hey Guys,
Good day. I would like to ask for some help, because I would like to create a column for WEEK Number for each dates, but I am getting incorrect values, e.g : March 26, 2017 to March 31, 2017, I am expecting to get WEEK 5 for those dates, but I am getting WEEK 1 which INCORRECT.
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertYearMonth = Table.AddColumn(InsertCalendarMonth, "YearMonthCalendar", each [Year] * 100 + [MonthOfYear]),
InsertCalendarQtr = Table.AddColumn(InsertYearMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
Source = Query1(#date(2017, 1, 1), #date(2017, 12, 31), null),
#"Inserted Week of Month" = Table.AddColumn(Source, "WeekOfMonth", each Date.WeekOfMonth([WeekEnding]), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Week of Month", "WeekNameMonth", each if [WeekOfMonth] = 1 then "Week 1"
else if [WeekOfMonth] = 2 then "Week 2"
else if [WeekOfMonth] = 3 then "Week 3"
else "Week 4"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"WeekNameMonth", "Weeks"}, {"DayOfWeekName", "Day of the Week"}})
SEE attached screenshot
THANK YOU SO MUCH!
Hey Guys,
Good day. I would like to ask for some help, because I would like to create a column for WEEK Number for each dates, but I am getting incorrect values, e.g : March 26, 2017 to March 31, 2017, I am expecting to get WEEK 5 for those dates, but I am getting WEEK 1 which INCORRECT.
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertYearMonth = Table.AddColumn(InsertCalendarMonth, "YearMonthCalendar", each [Year] * 100 + [MonthOfYear]),
InsertCalendarQtr = Table.AddColumn(InsertYearMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
Source = Query1(#date(2017, 1, 1), #date(2017, 12, 31), null),
#"Inserted Week of Month" = Table.AddColumn(Source, "WeekOfMonth", each Date.WeekOfMonth([WeekEnding]), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Week of Month", "WeekNameMonth", each if [WeekOfMonth] = 1 then "Week 1"
else if [WeekOfMonth] = 2 then "Week 2"
else if [WeekOfMonth] = 3 then "Week 3"
else "Week 4"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"WeekNameMonth", "Weeks"}, {"DayOfWeekName", "Day of the Week"}})
SEE attached screenshot
THANK YOU!!!
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 |
---|---|
92 | |
86 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
108 | |
64 | |
60 |