All,
Just did a video on YouTube that should be posted in the near future. Here is the M code that I referenced from that video:
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Ranges = {
{"All Dates",MinDates,MaxDates, 1},
{"Yesterday", Date.AddDays(TodaysDate,-1), Date.AddDays(TodaysDate,-1), 2},
{"Today", TodaysDate, TodaysDate, 3},
{"5D", Date.AddDays(TodaysDate, -4), TodaysDate, 4},
{"15D", Date.AddDays(TodaysDate, -14), TodaysDate, 5},
{"1M", Date.AddMonths(TodaysDate, -1), TodaysDate, 6},
{"3M", Date.AddMonths(TodaysDate, -3), TodaysDate, 7},
{"6M", Date.AddMonths(TodaysDate, -6), TodaysDate, 8},
{"YTD", Date.From(Date.StartOfYear(TodaysDate)), TodaysDate, 9},
{"1Y", Date.AddYears(TodaysDate, -1), TodaysDate, 10},
{"2Y", Date.AddYears(TodaysDate, -2), TodaysDate, 11}
},
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
The PBIX file shown in the video can be found on this thread on the forum:
Here is the post from that thread that contains the PBIX used in the video:
Thanks
Jarrett