All,
Here is a solution that I use on most of my models regarding a dynamic date range slicer. I found the solution from Chris Webb, from this blog post:
I changed up his solution a bit to match the solution I wanted. In order to set this up, you’ll need 2 different sets of M code. Read the blog to understand fully how this is done, but I wanted to point out that an important feature in the setup is the Relationship between the Table and the “Date Table”, Make sure in this case to have the Cross filter direction set to both.
Here are the two sets of M code you will need:
This is for the Query:
(
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
This is for the Table after the Query has been setup:
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Ranges = {
{"Today",
TodaysDate,
TodaysDate,
1},
{"Yesterday",
Date.AddDays(TodaysDate,-1),
Date.AddDays(TodaysDate,-1),
2},
{"Current Week to Date",
Date.From(Date.StartOfWeek(TodaysDate,1)),
TodaysDate,
3},
{"Previous Week",
Date.AddWeeks( Date.From(Date.StartOfWeek(TodaysDate,Day.Monday)),-1),
Date.From(Date.StartOfWeek(TodaysDate,Day.Sunday)),
4},
{"Current Month to Date",
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
5},
{"Previous Month",
Date.AddMonths( Date.From(Date.StartOfMonth(TodaysDate)), -1),
Date.AddMonths( Date.From(Date.EndOfMonth(TodaysDate)), -1),
6},
{"Current Year To Date",
Date.From(Date.StartOfYear(TodaysDate)),
TodaysDate,
7},
{"Previous Year To Date",
Date.AddYears(Date.From(Date.StartOfYear(TodaysDate)), -1),
Date.AddYears(Date.From(Date.StartOfDay(TodaysDate)), -1),
8},
{"Previous Year",
Date.AddYears(Date.From(Date.StartOfYear(TodaysDate)), -1),
Date.AddYears(Date.From(Date.EndOfYear(TodaysDate)), -1),
9}
},
GetTables = List.Transform(Ranges,
each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables)
in
Output
** I also create a DAX measure to show the selected Date Range from the slicer**
Date Range =
FORMAT ( MIN ( 'Date'[Date] ), "mm/dd/yyyy" ) & " - "
& FORMAT ( MAX ( 'Date'[Date] ), "mm/dd/yyyy" )
Here is a snapshot of what it looks like in a model I have setup:
Enjoy!
Jarrett
Also happy to be here from now on :))