Dynamic Date Range Slicer- Query M

Power Query M Code Showcase
6

Hi Tibbie,

You could nest both functions of course, then it will generate the Period table ‘automatically’
There’s no need to pass any actual parameters because they are all relative to TodaysDate = Date.From(DateTimeZone.FixedUtcNow()) and defined within the “Ranges”.
.

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Today", TodaysDate, TodaysDate, 1},
                {"Yesterday", Date.AddDays(TodaysDate, -1), Date.AddDays(TodaysDate, -1), 2},
                {"Last 7 Days", Date.AddDays(TodaysDate, -6), TodaysDate, 3}, // last 7 days including Today
                //{"Last 7 Days", Date.AddDays(TodaysDate, -7), Date.AddDays(TodaysDate, -1), 3}, // last 7 days excluding Today
                {"Current Week to Date", Date.From(Date.StartOfWeek(TodaysDate, Day.Monday)), TodaysDate, 4},
                {"Previous Week", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -1), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -1), Day.Monday)), 5},
                {"Prior Previous Week", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -2), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -2), Day.Monday)), 6},
                {"Current Month to Date", Date.From(Date.StartOfMonth(TodaysDate)), TodaysDate, 7},
                {"Previous Month", Date.From(Date.StartOfMonth(Date.AddMonths(TodaysDate, -1))), Date.From(Date.EndOfMonth(Date.AddMonths(TodaysDate, -1 ))), 8},
                {"Prior Previous Month", Date.From(Date.StartOfMonth(Date.AddMonths(TodaysDate, -2))), Date.From(Date.EndOfMonth(Date.AddMonths(TodaysDate, -2 ))), 9},
                {"Current Quarter to Date", Date.From(Date.StartOfQuarter(TodaysDate)), TodaysDate, 10},
                {"Previous Quarter", Date.From(Date.AddQuarters(Date.StartOfQuarter(TodaysDate), -1)), Date.From(Date.EndOfQuarter(Date.AddQuarters(TodaysDate, -1))), 11},
                {"Prior Previous Quarter", Date.From(Date.AddQuarters(Date.StartOfQuarter(TodaysDate), -2)), Date.From(Date.EndOfQuarter(Date.AddQuarters(TodaysDate, -2))), 12},
                {"Current Year To Date", Date.From(Date.StartOfYear(TodaysDate)), TodaysDate, 13},
                {"Previous Year To Date", Date.From(Date.AddYears(Date.StartOfYear(TodaysDate), -1)), Date.From(Date.AddYears(Date.StartOfDay(TodaysDate), -1)), 14},
                {"Previous Year", Date.From(Date.AddYears(Date.StartOfYear(TodaysDate), -1)), Date.From(Date.AddYears(Date.EndOfYear(TodaysDate), -1)), 15},
                {"Prior Previous Year", Date.From(Date.AddYears(Date.StartOfYear(TodaysDate), -2)), Date.From(Date.AddYears(Date.EndOfYear(TodaysDate), -2)), 16}
             },
    fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
        let
            DayCount = Duration.Days(EndDate-StartDate)+1,
            DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
            AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
            CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
        in
            CreateTable,
    
    GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
in
    Output

.
NOTES

  • Added some additional Ranges to the Period table in the M code above.
  • You can rename each Period Name by updating the first parameter of the corresponding line inside the “Ranges” section of the code.

I hope this is helpful.

7 Likes