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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Week Number is INCORRECT

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

 

 incorrect.PNG

 

THANK YOU!!!

2 ACCEPTED SOLUTIONS
v-caliao-msft
Employee
Employee

 

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

 

Regards,

Charlie Liao

View solution in original post

Anonymous
Not applicable

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

 

 incorrect.PNG

 

THANK YOU!!!

View solution in original post

8 REPLIES 8
rubymaya
Helper II
Helper II

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))

wmeyer
Helper III
Helper III

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:

 

Day number running = DATEDIFF ( DATE ( YEAR ( [Date] )| 1| 1 )| [Date]| DAY ) + 1
 
Week based on Day number running = IF ( INT((DateKey[Day number running] + 6) / 7) > 52|52|INT((DateKey[Day number running] + 6) / 7))
 
I'd like a better answer, one that i can understand (only been using BI for a couple of weeks) please, otherwise hope this helps someone.
v-caliao-msft
Employee
Employee

 

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

 

Regards,

Charlie Liao

Anonymous
Not applicable

Thank you so much!

Anonymous
Not applicable

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

 

incorrect.PNG

 

THANK YOU SO MUCH!

Anonymous
Not applicable

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

 

 incorrect.PNG

 

THANK YOU!!!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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