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

Create a date table where the week will be considered from Mon to Sat irrespective of any date

Hello All

 

I want to create a Date Table in Power bi Desktop where I want a week column in which Mon will be the first day and Sat will be the last day irrespective of any dates. In the week column, I want Week1, Week2... Week4

1 ACCEPTED SOLUTION
pi_eye
Resolver IV
Resolver IV

Hi @Anonymous, I believe this is what you are looking for:

pi_eye_0-1670070006962.png

 

You can use M Query to calculate the dates using the formula Date.WeekOfYear([Dates],1)

[Dates] = your date field

1 = starting day of week.

 

I created the calendar table using the guidance from this blog mssqltips.com/sqlservertip/6756/power-bi-calendar-table/ then used the dates it calculated to add the week columns.

 

Full code below - to use it, create a new blank query, and paste this into the advanced editor (option is in home -> query)

 

HTH, 

 

Pi

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
#"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow())),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Year", each Date.Year([Dates])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month([Dates])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "MonthName", each Date.MonthName([Dates])),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "ShortMonthName", each Text.Start([MonthName],3)),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Quarter", each Date.QuarterOfYear([Dates])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom6",{{"Quarter", type text}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type3", "QtrText", each "Qtr "& [Quarter]),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "WeekDayName", each Date.DayOfWeekName([Dates])),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "WeekDay", each Date.DayOfWeek([Dates])),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Week #", each Date.WeekOfYear([Dates],1)),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Custom", each "Week " & Number.ToText([#"Week #"]))
in
#"Added Custom11"

 

 

 

 

View solution in original post

1 REPLY 1
pi_eye
Resolver IV
Resolver IV

Hi @Anonymous, I believe this is what you are looking for:

pi_eye_0-1670070006962.png

 

You can use M Query to calculate the dates using the formula Date.WeekOfYear([Dates],1)

[Dates] = your date field

1 = starting day of week.

 

I created the calendar table using the guidance from this blog mssqltips.com/sqlservertip/6756/power-bi-calendar-table/ then used the dates it calculated to add the week columns.

 

Full code below - to use it, create a new blank query, and paste this into the advanced editor (option is in home -> query)

 

HTH, 

 

Pi

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
#"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow())),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Year", each Date.Year([Dates])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month([Dates])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "MonthName", each Date.MonthName([Dates])),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "ShortMonthName", each Text.Start([MonthName],3)),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Quarter", each Date.QuarterOfYear([Dates])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom6",{{"Quarter", type text}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type3", "QtrText", each "Qtr "& [Quarter]),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "WeekDayName", each Date.DayOfWeekName([Dates])),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "WeekDay", each Date.DayOfWeek([Dates])),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Week #", each Date.WeekOfYear([Dates],1)),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Custom", each "Week " & Number.ToText([#"Week #"]))
in
#"Added Custom11"

 

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors