Forum Discussion
Power Query Date Functions Help
- Anonymous6 years ago
I figured it out. New in red. Thanks!
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
ThisDayLastYear = Date.From(Date.AddYears(TodaysDate,-1)),
Ranges = {
{"Today",
TodaysDate,
TodaysDate,
1},
{"Current Week to Date",
Date.From(Date.StartOfWeek(TodaysDate)),
TodaysDate,
2},
{"Current Month to Date",
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
3},
{"Current Year to Date",
Date.From(Date.StartOfYear(TodaysDate)),
TodaysDate,
5},
{"Current Quarter to Date",
Date.From(Date.StartOfQuarter(TodaysDate)),
TodaysDate,
4},
{"Past 30 Days",
Date.AddDays(TodaysDate,-30) + #duration(1,0,0,0),
TodaysDate,
6},
{"Past 60 Days",
Date.AddDays(TodaysDate,-60) + #duration(1,0,0,0),
TodaysDate,
7},
{"Past 90 Days",
Date.AddDays(TodaysDate,-90) + #duration(1,0,0,0),
TodaysDate,
8},
{"Past 120 Days",
Date.AddDays(TodaysDate,-120) + #duration(1,0,0,0),
TodaysDate,
9},
{"YTD Last Year",
Date.From(Date.StartOfYear(ThisDayLastYear)),
ThisDayLastYear,
10}
},
GetTables = List.Transform(Ranges,
each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables),
#"Filtered Rows" = Table.SelectRows(Output, each ([Sort] = 10)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}})
in
#"Sorted Rows"
I have been able to get the 30/60/90/120 Days but still cannot figure out the correct syntax for Previous Year, Month & Quarter and Previous Year. Month and Quarter to Date.
Any help would be greatly appreciated!
[...]
{"Past 30 Days",
Date.AddDays(TodaysDate,-30) + #duration(1,0,0,0),
TodaysDate,
6},
{"Past 60 Days",
Date.AddDays(TodaysDate,-60) + #duration(1,0,0,0),
TodaysDate,
7},
{"Past 90 Days",
Date.AddDays(TodaysDate,-90) + #duration(1,0,0,0),
TodaysDate,
8},
{"Past 120 Days",
Date.AddDays(TodaysDate,-120) + #duration(1,0,0,0),
TodaysDate,
9}
[...]