Help with creating own date table

Power Query
1

Hi All,

I am new here so any help or direction would be great :slight_smile:

I am trying to create my own date table (by manipulating the table created my @Melissa) however I am struggling with Fiscal week number and Cal Week.

I have data that comes in once per week (on Mondays) however the Cal Week they provide is a week behind.

Wk Start = 07/09/2020
FY Week = 11
Cal Week = 36

Can anyone help with mcode for this? I know it’s probably simple, but I’ve spent too long trying to figure this out. Thanks in advance.2020-09-15T14:00:00Z

2

Hi @Nurry90

Could you please upload the pbix file and the output you are looking for.

3

Hi @Nurry90,

I’ve added a [Cal Weeknumber] but need more details on the FY Week…
Let’s see how you get on with this first.

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list, optional WDStartNum as number ) as table =>
  let
    FYStartMonth = if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1,
    //the WDStartNum parameter allows you to set Weekday numbering running from 0-6 or 1-7 but first day of the week will remain Monday
    WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
    CurrentDate = Date.From(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
    TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month & Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayOfWeek", each Date.DayOfWeek([Date]) + WDStart, Int64.Type),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekStart = Table.AddColumn(InsertDayName, "WeekStart", each Date.StartOfWeek( [Date], Day.Monday), type date),
    InsertWeekEnding = Table.AddColumn(InsertWeekStart, "WeekEnding", each Date.EndOfWeek( [Date], Day.Monday), type date),
    InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertMonthEnding, "ISO Weeknumber", each
      if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
      then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
      else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
      then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
    InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )),  Int64.Type),
    BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[ISO Year], [DateInt]])),
    InsertISOday = Table.AddColumn(InsertISOyear, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])),  Int64.Type),
    InsertCalendarWk = Table.AddColumn(InsertISOday, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), type text ),
    InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100,  Int64.Type),
    InsertCalWeekNumber= Table.AddColumn(InsertWeeknYear, "Cal Weeknumber", each
      if Number.RoundDown((Date.DayOfYear(Date.AddDays([Date],-7))-(Date.DayOfWeek(Date.AddDays([Date], -7), Day.Monday)+1)+10)/7)=0
      then Number.RoundDown((Date.DayOfYear(#date(Date.Year(Date.AddDays([Date], -7))-1,12,31))-(Date.DayOfWeek(#date(Date.Year(Date.AddDays([Date], -7))-1,12,31), Day.Monday)+1)+10)/7)
      else if (Number.RoundDown((Date.DayOfYear(Date.AddDays([Date], -7))-(Date.DayOfWeek(Date.AddDays([Date], -7), Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year(Date.AddDays([Date], -7)),12,31), Day.Monday)+1<4))
      then 1 else Number.RoundDown((Date.DayOfYear(Date.AddDays([Date], -7))-(Date.DayOfWeek(Date.AddDays([Date], -7), Day.Monday)+1)+10)/7), type number),

    InsertMonthnYear = Table.AddColumn(InsertCalWeekNumber , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    AddFY = Table.AddColumn(InsertQuarternYear, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),
    AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
    AddFM = Table.AddColumn(AddFQ, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth then [MonthOfYear] - (FYStartMonth-1) else [MonthOfYear] + (12-FYStartMonth+1), type text),

    InsertIsAfterToday = Table.AddColumn(AddFM, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
    InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
    InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
    InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
    InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),

    //InsertDayOffset = Table.AddColumn(InsertDayType, "DayOffset", each Number.From([Date] - CurrentDate), type number),  //if you enable DayOffset, don't forget to adjust the PreviousStepName in the next line of code.
    InsertWeekOffset = Table.AddColumn(InsertDayType, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number),
    InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) +  Date.Month(Date.From(CurrentDate))), type number),
    InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) +  Date.QuarterOfYear(Date.From(CurrentDate))), type number),
    InsertYearOffset = Table.AddColumn(InsertQuarterOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),

    IdentifyCurrentDate = Table.SelectRows(InsertYearOffset, each ([Date] = CurrentDate)),
    CurrentYear = IdentifyCurrentDate{0}[Year],
    CurrentMonth = IdentifyCurrentDate{0}[MonthOfYear],
    InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - 
      (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
    RemoveToday = if EndDate < CurrentDate then Table.SelectRows(InsertFYoffset, each ([Date] <> CurrentDate)) else InsertFYoffset,
    InsertCompletedWeek = Table.AddColumn(RemoveToday, "WeekCompleted", each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), type logical),
    InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "MonthCompleted", each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
    InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
    InsertChangedType = Table.TransformColumnTypes(InsertCompletedQuarter,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}})
  in
  InsertChangedType
in
fnDateTable

I hope this is helpful

4

Hi Melissa,

Thank you for that, I have added it to my date table and is working fine :slight_smile:

For Financial year week, I’m not sure what my criteria is yet. Our supplier sometimes has 53 weeks (very rarely). The last time this happened was FY19. My FY is 1st July to 30th June. I’m guessing i need to put together a code that if 25th June is Monday then FY Week = 53 and base all other weeks around this, i.e 2nd July will be FY week 1 and so on. Below is what I have so far.

let
StartDateTime = #datetime(2018,06,25,12,00,00),
EndDateTime = #datetime(Date.Year(DateTimeZone.FixedLocalNow())+1,06,30,12,00,00),
StartDate = #date(2018,06,25),
EndDate = #date(Date.Year(DateTimeZone.FixedLocalNow())+1,06,30),
FYStartMonthNo = 7,

FYStartMonth = try if List.Contains({1..12}, FYStartMonthNo) then FYStartMonthNo else 1 otherwise 1,
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDateTime-StartDateTime))+1,
Source = List.DateTimes(StartDateTime, DayCount, #duration(7,0,0,0)),
ChangeListToTable = Table.FromList(Source, Splitter.SplitByNothing()),
ChangeType = Table.TransformColumnTypes(ChangeListToTable, {{"Column1" , type datetime}}),
ChangedName = Table.RenameColumns(ChangeType, {{"Column1", "DateLoad"}}),
#"Duplicated Column" = Table.DuplicateColumn(ChangedName, "DateLoad", "DateLoad - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"DateLoad - Copy", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"DateLoad - Copy", "Date"}}),
AddYear = Table.AddColumn( #"Renamed Columns", "Year", each Date.Year([Date]), type number),
AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), type number),
AddDay = Table.AddColumn(AddMonth, "Day", each Date.Day([Date]), type number),
#"Wk Start" = Table.AddColumn(AddDay, "Wk Start", each [Year] * 10000 + [Month] * 100 + [Day], type number),
InsertMonthName = Table.AddColumn(#"Wk Start", "Month Name", each Date.ToText([Date], "MMMM"), type text),
InsertCalWeek = Table.AddColumn(InsertMonthName, "Cal Wk", each 
if Number.RoundDown((Date.DayOfYear(Date.AddDays([Date],-7))-(Date.DayOfWeek(Date.AddDays([Date], -7), Day.Monday)+1)+10)/7)=0
then Number.RoundDown((Date.DayOfYear(#date(Date.Year(Date.AddDays([Date], -7))-1,12,31))-(Date.DayOfWeek(#date(Date.Year(Date.AddDays([Date], -7))-1,12,31), Day.Monday)+1)+10)/7)
else if (Number.RoundDown((Date.DayOfYear(Date.AddDays([Date], -7))-(Date.DayOfWeek(Date.AddDays([Date], -7), Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year(Date.AddDays([Date], -7)),12,31), Day.Monday)+1<4))
then 1 else Number.RoundDown((Date.DayOfYear(Date.AddDays([Date], -7))-(Date.DayOfWeek(Date.AddDays([Date], -7), Day.Monday)+1)+10)/7), type number),
#"Filtered Rows" = Table.SelectRows(InsertCalWeek, each true)

in
#“Filtered Rows”

5

Hi @Melissa,

Sorry I was incorrect, my criteria is Monday 24th June, not 25th. #duration is (1,0,0,0). I now have a column, WeekBeginningDayNumber (1-30 for June) and WeekBeginningMonthNumber (i.e. 6 is June).

If (WeekBeginningDayNumber = 24 and WeekBeginningMonthNumber = 6) then week 53 occurs (at the minute I have this as ([Cal Wk] +28)) if not then I need 1-52 weeks.

Sorry if this is confusing.