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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
misen13
Frequent Visitor

Week of month from Calendar

Hi,

 

Is there a way to create a calendar of unique value of weeks?

IE: 01.01.2018 to 31.12.08 shows a column with 1/2018, 2/2018...52/2018

 

Thanks.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This is very easy to do in Power Query using Date.WeekOfYear

 

 

The following PQ code will create a date table with the column you want

let
    EndDate = #date(2018,12,31),
    StartDate = #date(2018,1,1),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
    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"}}),
    AddedYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),Int64.Type),
    AddedWeekOfYear = Table.AddColumn(AddedYear, "WeekOfYear", each Date.WeekOfYear([Date]),Int64.Type),
    AddedWeekInYear = Table.AddColumn(AddedWeekOfYear, "WeekInYear", each Text.From([WeekOfYear]) & "/" & Text.From([Year]),Text.Type)
in
    AddedWeekInYear  

View solution in original post

2 REPLIES 2
Omega
Impactful Individual
Impactful Individual

@misen13 

 

Try creating a calculated table and call it calendar: 

 

Calendar = CALENDAR("01/01/2018","01/01/2030")

Then, create a calulcated column for weeks: 

 

Week = WEEKNUM('Calendar'[Date],2) &"/"&YEAR('Calendar'[Date])
Anonymous
Not applicable

This is very easy to do in Power Query using Date.WeekOfYear

 

 

The following PQ code will create a date table with the column you want

let
    EndDate = #date(2018,12,31),
    StartDate = #date(2018,1,1),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
    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"}}),
    AddedYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),Int64.Type),
    AddedWeekOfYear = Table.AddColumn(AddedYear, "WeekOfYear", each Date.WeekOfYear([Date]),Int64.Type),
    AddedWeekInYear = Table.AddColumn(AddedWeekOfYear, "WeekInYear", each Text.From([WeekOfYear]) & "/" & Text.From([Year]),Text.Type)
in
    AddedWeekInYear  

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.