Comparison Date (Year on Year)

[JPAnalytics]

Bit of a complex one, but I am trying to create a Comparison Date in my Calendar Table so individual days can be compared year over year with the current year's date being the end result. The comparison period itself needs to be based off the Week (ISO) column.

Below is small example of what I am trying to achieve. For Week (ISO) = 1, the Comparison Date column is the end result I am after.

let
  Source = List.Dates(#date(2018, 1, 1), 100000, #duration(1, 0, 0, 0)),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "Date"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] <= Date.EndOfYear(Date.From(DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), +10))))),
  #"Week Day" = Table.AddColumn(#"Filtered Rows", "Week Day", each Date.DayOfWeekName([Date])),
  #"Week Day Short" = Table.AddColumn(#"Week Day", "Week Day Short", each Text.Start([Week Day], 3)),
  #"Week Day No." = Table.AddColumn(#"Week Day Short", "Week Day No.", each Date.DayOfWeek([Date],Day.Monday)+1),
  Week = Table.AddColumn(#"Week Day No.", "Week", each Date.WeekOfYear([Date], Day.Sunday)),
  #"Week (ISO)" = Table.AddColumn(Week, "Week (ISO)", 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)),
  #"Changed Type1" = Table.TransformColumnTypes(#"Week (ISO)",{{"Date", type date}, {"Week Day", type text}, {"Week Day Short", type text}, {"Week Day No.", Int64.Type}, {"Week", Int64.Type}, {"Week (ISO)", Int64.Type}}),
  #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([#"Week (ISO)"] = 1)),
  #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "Comparison Date", each if [Week Day] = "Monday" then #date(2022, 1, 3) else if [Week Day] = "Tuesday" then #date(2022, 1, 4) else if [Week Day] = "Wednesday" then #date(2022, 1, 5) else if [Week Day] = "Thursday" then #date(2022, 1, 6) else if [Week Day] = "Friday" then #date(2022, 1, 7) else if [Week Day] = "Saturday" then #date(2022, 1,  else if [Week Day] = "Sunday" then #date(2022, 1, 9) else null, type date)
in
  #"Added Conditional Column"

[AlB]

@JPAnalytics 

inner is just how I am naming the input parameter to the function. Yo could choose any other name. Note we have the Table.SelectRows( ) within an each.

Each is actually short for

        (_) =>

which is essentially declaring a function with an input parameter called "_"

We need the inner to differentiate between the current row value in the outer table (the one we are adding the custom column to) and row values in the the inner table (the one we are scanning to extract the values we want: #"Changed Type1)

I do recommend reading this excellent article; everything will be much clearer: 

https://www.excelguru.ca/blog/2018/01/09/each-keyword-power-query/

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@JPAnalytics 

Try this to constrain the resutl to the current year rather than to the latest year in the table. It's only the last step that changes from the previous version:

let
  Source = List.Dates(#date(2018, 1, 1), 100000, #duration(1, 0, 0, 0)),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "Date"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] <= Date.EndOfYear(Date.From(DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), +10))))),
  #"Week Day" = Table.AddColumn(#"Filtered Rows", "Week Day", each Date.DayOfWeekName([Date])),
  #"Week Day Short" = Table.AddColumn(#"Week Day", "Week Day Short", each Text.Start([Week Day], 3)),
  #"Week Day No." = Table.AddColumn(#"Week Day Short", "Week Day No.", each Date.DayOfWeek([Date],Day.Monday)+1),
  Week = Table.AddColumn(#"Week Day No.", "Week", each Date.WeekOfYear([Date], Day.Sunday)),
  #"Week (ISO)" = Table.AddColumn(Week, "Week (ISO)", 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)),
  #"Changed Type1" = Table.TransformColumnTypes(#"Week (ISO)",{{"Date", type date}, {"Week Day", type text}, {"Week Day Short", type text}, {"Week Day No.", Int64.Type}, {"Week", Int64.Type}, {"Week (ISO)", Int64.Type}}),
  #"Added Custom" = Table.AddColumn(#"Changed Type1", "Comparison date", each List.Max(Table.SelectRows(#"Changed Type1", (inner) => [#"Week (ISO)"] = inner[#"Week (ISO)"] and [#"Week Day No."] = inner[#"Week Day No."] and Date.Year(inner[Date]) = Date.Year(DateTime.LocalNow()))[Date]), type date)
in
    #"Added Custom"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[JPAnalytics]

Thanks so much, love the elegant solution.

Will definitely read that article too.

My one reservation about adopting this calendar is that it is super resource hungry.

The calendar I shared with you forms part of a large calendar full of different functions, the old calendar takes 4 seconds to to refresh as a dataflow, the new one with the new column takes 11 minutes.

I get pretty close by merging the calendar on itself however it duplications a few dates.

let
  Source = List.Dates(#date(2000, 1, 1), 100000, #duration(1, 0, 0, 0)),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "Date"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] <= Date.EndOfYear(Date.From(DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), +10))))),
  #"Week Day" = Table.AddColumn(#"Filtered Rows", "Week Day", each Date.DayOfWeekName([Date])),
  #"Week Day Short" = Table.AddColumn(#"Week Day", "Week Day Short", each Text.Start([Week Day], 3)),
  #"Week Day No." = Table.AddColumn(#"Week Day Short", "Week Day No.", each Date.DayOfWeek([Date],Day.Monday)+1),
  Week = Table.AddColumn(#"Week Day No.", "Week", each Date.WeekOfYear([Date], Day.Sunday)),
  #"Week (ISO)" = Table.AddColumn(Week, "Week (ISO)", 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)),
  #"Added Custom1" = Table.AddColumn(#"Week (ISO)", "Reporting Year", each Date.Year(Date.AddDays([Date], 26 - [#"Week (ISO)"]))),
  #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Week (ISO)", "Week Day No."}, #"Added Custom1", {"Week (ISO)", "Week Day No."}, "Calendar", JoinKind.LeftOuter),
  #"Expanded Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Calendar", {"Date"}, {"Comparison Date"}),
  #"Filtered Rows1" = Table.SelectRows(#"Expanded Calendar", each Date.IsInCurrentYear([Comparison Date])),
  #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [Date] = #date(2018, 12, 29) or [Date] = #date(2019, 12, 28)),
  #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"Date", Order.Ascending}}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date", type date}, {"Week Day", type text}, {"Week Day Short", type text}, {"Week Day No.", Int64.Type}, {"Week", Int64.Type}, {"Week (ISO)", Int64.Type}, {"Comparison Date", type date}})
in
  #"Changed Type1"

[AlB]

11 minutes???

Yes, a merge should do better. I've taken the duplicates out by keeping the latest entry thta I think is the correct one but you might have to tweak it a bit

let
  Source = List.Dates(#date(2018, 1, 1), 100000, #duration(1, 0, 0, 0)),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "Date"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] <= Date.EndOfYear(Date.From(DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), +10))))),
  #"Week Day" = Table.AddColumn(#"Filtered Rows", "Week Day", each Date.DayOfWeekName([Date])),
  #"Week Day Short" = Table.AddColumn(#"Week Day", "Week Day Short", each Text.Start([Week Day], 3)),
  #"Week Day No." = Table.AddColumn(#"Week Day Short", "Week Day No.", each Date.DayOfWeek([Date],Day.Monday)+1),
  Week = Table.AddColumn(#"Week Day No.", "Week", each Date.WeekOfYear([Date], Day.Sunday)),
  #"Week (ISO)" = Table.AddColumn(Week, "Week (ISO)", 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)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Week (ISO)",{{"Date", type date}, {"Week Day", type text}, {"Week Day Short", type text}, {"Week Day No.", Int64.Type}, {"Week", Int64.Type}, {"Week (ISO)", Int64.Type}}),
  currentYear_ = Date.Year(DateTime.LocalNow()), 
  auxT_ = Table.SelectColumns(Table.SelectRows(#"Changed Type1", each Date.Year([Date]) = currentYear_), {"Date", "Week Day No.", "Week (ISO)"}),
  #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Week Day No.", "Week (ISO)"}, auxT_, {"Week Day No.", "Week (ISO)"}, "Query1 (2)", JoinKind.LeftOuter),
    #"Expanded Query1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Query1 (2)", {"Date"}, {"Query1 (2).Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Query1 (2)",{{"Date", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Date"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"Date", Order.Ascending}})
in
    #"Sorted Rows1"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[JPAnalytics]

That is awesome, thank you so much. I really appreciate your help.

Would be great if you could explain how that custom column is working, as I haven't seen "inner" used before.

One thing, is it possible to have the Comparison Date to show for this year (when its next year roll to 2023 etc.) so future dates in the calendar work? I am guessing incorporating something like the below so it doesn't go past the end of the current year.

Date.EndOfYear(Date.From(DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), +10))))

For example if you expand the calendar out now the comparison date goes to the max date of the calendar.

[AlB]

Hi @JPAnalytics 

Not sure I've understood completely, but give this a  go:

let
  Source = List.Dates(#date(2018, 1, 1), 100000, #duration(1, 0, 0, 0)),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "Date"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Date] <= Date.EndOfYear(Date.From(DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), +10))))),
  #"Week Day" = Table.AddColumn(#"Filtered Rows", "Week Day", each Date.DayOfWeekName([Date])),
  #"Week Day Short" = Table.AddColumn(#"Week Day", "Week Day Short", each Text.Start([Week Day], 3)),
  #"Week Day No." = Table.AddColumn(#"Week Day Short", "Week Day No.", each Date.DayOfWeek([Date],Day.Monday)+1),
  Week = Table.AddColumn(#"Week Day No.", "Week", each Date.WeekOfYear([Date], Day.Sunday)),
  #"Week (ISO)" = Table.AddColumn(Week, "Week (ISO)", 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)),
  #"Changed Type1" = Table.TransformColumnTypes(#"Week (ISO)",{{"Date", type date}, {"Week Day", type text}, {"Week Day Short", type text}, {"Week Day No.", Int64.Type}, {"Week", Int64.Type}, {"Week (ISO)", Int64.Type}}),
  #"Added Custom" = Table.AddColumn(#"Changed Type1", "Comparison date", each List.Max(Table.SelectRows(#"Changed Type1", (inner) => [#"Week (ISO)"] = inner[#"Week (ISO)"] and [#"Week Day No."] = inner[#"Week Day No."])[Date]), type date)
in
    #"Added Custom" 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.