Query for Reporting Date Cycles

[Anonymous]

Hello,

Looking to speed up the time it would take me to do this myself, and wondered if they had a query they could shre for Reporting.

Ideally

Date, Year, Month Name, Quarter, Week Number, Day NO, Day Name, Day of Week, Fiscal year, Fiscal Period, FIscal Quarter, Fiscal Year & Quarter, Week Ending and Week commencing.

Any help is gratefully appreciated (oh and UK)

[Watsky · Accepted solution]

Hey @Anonymous ,

I have one to get you started. However, you didn't explain to us what month the fiscal period begins also if the fiscal period is based on the fiscal year/fiscal quarter  or what date you want to start the calendar. With that said here's what I came up with starting 1/1/2020.

Change the FiscalMonth to the starting month of your Fiscal Calendar and change the Source date to the date you want the calendar to begin.

let
    Source = #date(2020,1,1),
   
    FiscalMonth = 9,
    #"Convert to List" = List.Dates(Source, Number.From(DateTime.LocalNow())-Number.From(Source),#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(#"Convert to List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Column to Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date Range"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Column to Date",{{"Date Range", type date}}, "en-GB"),
    #"Inserted Year" = Table.AddColumn(#"Changed Type to Date", "Year", each Date.Year([Date Range]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date Range]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date Range]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted End of Year", "End of Month", each Date.EndOfMonth([Date Range]), type date),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted End of Month", "Month Name", each Date.MonthName([Date Range]), type text),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Month Name", "Start of Week", each Date.StartOfWeek([Date Range]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date Range]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted End of Week", "End of Quarter", each Date.EndOfQuarter([Date Range]), type date),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted End of Quarter", "Quarter",  each  "Q" & Number.ToText(Date.QuarterOfYear([Date Range])),type text),
    #"Inserted Weekday" = Table.AddColumn(#"Inserted Quarter", "Day Name", each Date.DayOfWeekName([Date Range]), type text),
    #"Inserted Month Number" = Table.AddColumn(#"Inserted Weekday", "Month Number", each Date.Month([Date Range]), Int64.Type),
    #"Inserted Fiscal Month" = Table.AddColumn(#"Inserted Month Number", "Fiscal Month", each if(FiscalMonth <= [Month Number]) then [Month Number] - FiscalMonth +1 else (12 - FiscalMonth+1)+[Month Number], Int64.Type),
    #"Inserted Full Fiscal Month" = Table.AddColumn(#"Inserted Fiscal Month", "Full Fiscal Month", each if [Fiscal Month]< 10 then Text.Combine({"0", Text.From([Fiscal Month], "en-GB")}) else Text.From([Fiscal Month],"en-GB"), Int64.Type),
    #"Inserted Fiscal Start Month" = Table.AddColumn(#"Inserted Fiscal Month", "BeginFiscalYear", each FiscalMonth, Int64.Type),
    #"Inserted Fiscal Year" = Table.AddColumn(#"Inserted Fiscal Start Month", "Fiscal Year", each if (FiscalMonth <= [Month Number]) then [Year]+1 else [Year], Int64.Type),
    #"Inserted Beginning of Fiscal Year" = Table.AddColumn(#"Inserted Fiscal Year", "Beginning Fiscal Year", each Date.FromText(Text.Combine({Text.From([BeginFiscalYear], "en-GB"), "/", "1", "/", Text.From([Year]-1, "en-GB")})), type date),
    #"Inserted Day of Fiscal Year" = Table.AddColumn(#"Inserted Beginning of Fiscal Year", "Day of Fiscal Year", each Duration.Days([Date Range]- [Beginning Fiscal Year]), Int64.Type),
    #"Inserted Week of Fiscal Year" = Table.AddColumn(#"Inserted Day of Fiscal Year", "Week of Fiscal Year", each Number.Round([Day of Fiscal Year]/7,0,0), Int64.Type),
    #"Inserted Quarter of Fiscal Year" = Table.AddColumn(#"Inserted Week of Fiscal Year", "Quarter of Fiscal Year", each "Q" & Number.ToText(Number.Round(([Fiscal Month]+1)/3,0,0)), type text)
in
    #"Inserted Quarter of Fiscal Year"

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[Anonymous]

Thank you so much everyone!

[mussaenda · Accepted solution]

Hi @Anonymous,

You can try this function. This is what I am using for years now. This is from enterprise dna site. I just amended the Week Number because I need to follow the ISO week.

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    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]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    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"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "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"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", 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)),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
    AddFY
in
    fnDateTable

Hope this helps!

[Watsky · Accepted solution]

Hey @Anonymous ,

I have one to get you started. However, you didn't explain to us what month the fiscal period begins also if the fiscal period is based on the fiscal year/fiscal quarter  or what date you want to start the calendar. With that said here's what I came up with starting 1/1/2020.

Change the FiscalMonth to the starting month of your Fiscal Calendar and change the Source date to the date you want the calendar to begin.

let
    Source = #date(2020,1,1),
   
    FiscalMonth = 9,
    #"Convert to List" = List.Dates(Source, Number.From(DateTime.LocalNow())-Number.From(Source),#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(#"Convert to List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Column to Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date Range"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Column to Date",{{"Date Range", type date}}, "en-GB"),
    #"Inserted Year" = Table.AddColumn(#"Changed Type to Date", "Year", each Date.Year([Date Range]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date Range]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date Range]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted End of Year", "End of Month", each Date.EndOfMonth([Date Range]), type date),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted End of Month", "Month Name", each Date.MonthName([Date Range]), type text),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Month Name", "Start of Week", each Date.StartOfWeek([Date Range]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date Range]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted End of Week", "End of Quarter", each Date.EndOfQuarter([Date Range]), type date),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted End of Quarter", "Quarter",  each  "Q" & Number.ToText(Date.QuarterOfYear([Date Range])),type text),
    #"Inserted Weekday" = Table.AddColumn(#"Inserted Quarter", "Day Name", each Date.DayOfWeekName([Date Range]), type text),
    #"Inserted Month Number" = Table.AddColumn(#"Inserted Weekday", "Month Number", each Date.Month([Date Range]), Int64.Type),
    #"Inserted Fiscal Month" = Table.AddColumn(#"Inserted Month Number", "Fiscal Month", each if(FiscalMonth <= [Month Number]) then [Month Number] - FiscalMonth +1 else (12 - FiscalMonth+1)+[Month Number], Int64.Type),
    #"Inserted Full Fiscal Month" = Table.AddColumn(#"Inserted Fiscal Month", "Full Fiscal Month", each if [Fiscal Month]< 10 then Text.Combine({"0", Text.From([Fiscal Month], "en-GB")}) else Text.From([Fiscal Month],"en-GB"), Int64.Type),
    #"Inserted Fiscal Start Month" = Table.AddColumn(#"Inserted Fiscal Month", "BeginFiscalYear", each FiscalMonth, Int64.Type),
    #"Inserted Fiscal Year" = Table.AddColumn(#"Inserted Fiscal Start Month", "Fiscal Year", each if (FiscalMonth <= [Month Number]) then [Year]+1 else [Year], Int64.Type),
    #"Inserted Beginning of Fiscal Year" = Table.AddColumn(#"Inserted Fiscal Year", "Beginning Fiscal Year", each Date.FromText(Text.Combine({Text.From([BeginFiscalYear], "en-GB"), "/", "1", "/", Text.From([Year]-1, "en-GB")})), type date),
    #"Inserted Day of Fiscal Year" = Table.AddColumn(#"Inserted Beginning of Fiscal Year", "Day of Fiscal Year", each Duration.Days([Date Range]- [Beginning Fiscal Year]), Int64.Type),
    #"Inserted Week of Fiscal Year" = Table.AddColumn(#"Inserted Day of Fiscal Year", "Week of Fiscal Year", each Number.Round([Day of Fiscal Year]/7,0,0), Int64.Type),
    #"Inserted Quarter of Fiscal Year" = Table.AddColumn(#"Inserted Week of Fiscal Year", "Quarter of Fiscal Year", each "Q" & Number.ToText(Number.Round(([Fiscal Month]+1)/3,0,0)), type text)
in
    #"Inserted Quarter of Fiscal Year"

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[Anonymous]

Thank you so much!