cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alopez Regular Visitor
Regular Visitor

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

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: Week Number is INCORRECT

 

@alopez,

 

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

alopez Regular Visitor
Regular Visitor

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

View solution in original post

7 REPLIES 7
alopez Regular Visitor
Regular Visitor

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

View solution in original post

alopez Regular Visitor
Regular Visitor

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 SO MUCH!

Moderator v-caliao-msft
Moderator

Re: Week Number is INCORRECT

 

@alopez,

 

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

alopez Regular Visitor
Regular Visitor

Re: Week Number is INCORRECT

Thank you so much!

Highlighted
wmeyer Regular Visitor
Regular Visitor

Re: Week Number is INCORRECT

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. 

ovendoor Frequent Visitor
Frequent Visitor

Re: Week Number is INCORRECT

Did you solve the week number 53 isses? I'm getting the same problem.

ovendoor Frequent Visitor
Frequent Visitor

Re: Week Number is INCORRECT

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.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors