Forum Discussion
Calendar +1 Year from current date
Hi, I created calendar using this Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQNzDWNzIwNFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
#"Added EndDate" = Table.AddColumn(#"Changed Type", "EndDate", each Date.From(DateTime.LocalNow())),
#"Changed Type1" = Table.TransformColumnTypes(#"Added EndDate",{{"EndDate", type date}}),
#"Added Dates" = Table.AddColumn(#"Changed Type1", "Dates", each {Number.From([Start Date])..Number.From([EndDate])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Dates", "Dates"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Start Date", "EndDate"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Dates", Order.Descending}}),
#"Added Year" = Table.AddColumn(#"Sorted Rows", "Year", each Date.Year([Dates])),
#"Added Month" = Table.AddColumn(#"Added Year", "Month", each Date.Month([Dates])),
#"Added Month Name" = Table.AddColumn(#"Added Month", "Month Name", each Date.MonthName([Dates])),
#"Added Shorth Month Name" = Table.AddColumn(#"Added Month Name", "Short Month Name", each Text.Start([Month Name],3)),
#"Added Quarter" = Table.AddColumn(#"Added Shorth Month Name", "Quarter", each Date.QuarterOfYear([Dates])),
#"Changed Quarter type to text" = Table.TransformColumnTypes(#"Added Quarter",{{"Quarter", type text}}),
#"Added QtrText" = Table.AddColumn(#"Changed Quarter type to text", "QtrText", each "Qtr " & [Quarter]),
#"Added Week of Year" = Table.AddColumn(#"Added QtrText", "Week of Year", each Date.WeekOfYear([Dates])),
#"Added Day of Year" = Table.AddColumn(#"Added Week of Year", "Day of Year", each Date.DayOfYear([Dates])),
#"Added Day of Month" = Table.AddColumn(#"Added Day of Year", "Day of Month", each Date.Day([Dates])),
#"Added Week of Month" = Table.AddColumn(#"Added Day of Month", "Week of Month", each Date.WeekOfMonth([Dates])),
#"Added Day of Week" = Table.AddColumn(#"Added Week of Month", "Day of Week", each Date.DayOfWeek([Dates])+1),
#"Added Day of Week Name" = Table.AddColumn(#"Added Day of Week", "Day of Week Name", each Date.DayOfWeekName([Dates])),
#"Added Day of Week Sun-Sat" = Table.AddColumn(#"Added Day of Week Name", "Day of Week Sun-Sat", each Date.DayOfWeek([Dates],0)+1),
#"Added Week of Year Sun-Sat" = Table.AddColumn(#"Added Day of Week Sun-Sat", "Week of Year Sun-Sat", each Date.WeekOfYear([Dates],0)),
#"Reordered Columns" = Table.ReorderColumns(#"Added Week of Year Sun-Sat",{"Dates", "Day of Week", "Day of Week Name", "Day of Week Sun-Sat", "Day of Month", "Day of Year", "Week of Month", "Week of Year Sun-Sat", "Week of Year", "Month", "Month Name", "Short Month Name", "Quarter", "QtrText", "Year"})
in
#"Reordered Columns"
It works ok for one of my reports, but other reports have data that exist in the future time.
I don't know how to modify this part of the query to have +1 year from LocalNow date that it gives.
#"Added EndDate" = Table.AddColumn(#"Changed Type", "EndDate", each Date.From(DateTime.LocalNow()))
If anyone know how to modify, I would appreciate the help.
Thanks
AlienSx Thanks it worked.
I did try to move it before but it wasnt working for some reason. Maybe someting else was messed up.
Here is working Query:
Table.AddColumn(#"Changed Type", "EndDate", each Date.AddYears(Date.From(DateTime.LocalNow()), 1))
5 Replies
- AnonymousNot applicable
#"Added EndDate" = Table.AddColumn(#"Changed Type", "EndDate", each Date.AddYears(Date.From(DateTime.LocalNow())), 1)
--Nate
- Justas4478Post Prodigy
Anonymous Hi I am getting this error.
I am not sure what is wrong
- AlienSxSuper User
Justas4478 move , 1 to the left. Must be , 1)) in the end.