Dynamic Date Range Slicer- Query M

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.

image

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:

image

Enjoy!
Jarrett

3 Likes

Just marking this as a solved solution.

Thanks
Jarrett

Hi Jarrett,

Thanks so much for sharing!
Used this technique for the first time today in one of my models, absolutely fabulous. Think it’s a great way to facilitate report users, will do this more often :wink:

1 Like