Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power Query Date Functions Help

Hello!   I am trying to create a query that provides different time-intelligence periods (MTD, YTD, etc.). I have been able to get a few but am unable to figure out the remaining ones. Can you plea...
  • Anonymous's avatar
    Anonymous
    6 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"