Week of month from Calendar

[misen13]

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.

[Omega]

@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 · Accepted solution]

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