Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JPAnalytics
Regular Visitor

Comparison Date (Year on Year)

‎06-04-2022 09:36 PM

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"

 

 

 

Labels:
Message 1 of 7
448 Views
0
6 REPLIES 6
AlB
Super User
Super User

‎06-05-2022 03:18 AM

@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/

 

SU18_powerbi_badge

 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.

 

Message 7 of 7
378 Views
AlB
Super User
Super User

‎06-05-2022 03:09 AM

@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"

SU18_powerbi_badge

 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.

 

Message 4 of 7
383 Views
JPAnalytics
Regular Visitor
In response to AlB

‎06-05-2022 03:55 AM

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"
Message 5 of 7
367 Views
0
AlB
Super User
Super User
In response to JPAnalytics

‎06-05-2022 09:13 AM

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"

SU18_powerbi_badge

 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.

 

Message 6 of 7
355 Views
JPAnalytics
Regular Visitor

‎06-05-2022 02:44 AM

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.

 

Message 3 of 7
391 Views
0
AlB
Super User
Super User

‎06-05-2022 12:50 AM

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"

 

SU18_powerbi_badge

 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.

 

Message 2 of 7
396 Views

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All