Forum Discussion
Comparison Date (Year on Year)
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 compari...
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. |
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"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.
