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

8 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

@JarrettM Thanks so much for sharing!

Is there a way to break down the M-code to not have to create a function which then calls another query?

I would like something similar to this which i use for our current DateTable:

let
  Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
  CurrentDate = Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -1),
  StartDate = #date(2014, 1, 1),
  EndDate = #date(2022, 1, 1),
  Length = Duration.Days(EndDate - StartDate),
  DateTable = #"Changed Type",
  #"Insert RelativeDate" = Table.AddColumn(DateTable, "RelativeDate", each Date.AddDays(DateTime.Date([Date]), -1)),
  ...
  ...
in #"Insert RelativeDate"

@Tibbie,

To the best of my knowledge this is the way to accomplish what needs to be done. Please refer to the article from Chris Webb if you have any questions on the how, or why, of how this was done.

Thanks
Jarrett

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.

4 Likes

Great work @Melissa, thanks for the help with that!

We are using PBI data flows so it would be more convenient to have it as one query instead of one calling another in the dataflow.

@JarrettM I love this dynamic date range slice, very useful, Thanks.

1 Like

how does this related to the creating of the date table @Melissa at the start of setting up the date table?

thanks Keith

Hi @Keith,

Have you looked over the article by Chris Webb in the first post of this thread?

The Dynamic Date Ranges you create are actually used to filter the Date table.
If you use the nested version, 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”.

Where a Date table is required in most Models a Period table is optional.

I hope this is helpful.

Hi @Melissa,

No, i didn’t see it but i’ll have a look at.
thanks
Keith

Hi @cbchow,
Please start a new topic as this string has a solution to it.

thanks
Keith

Will do, thanks: Dynamic Date Range Slicer conflicting with outside period references

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

3 Likes

Hello everyone.
I have another question. When period selected through this slicer, no time intelligence measures are working. For example, Same Period Last Year returns blank.
Does anyone now a way to implement it? The idea is to select, for example This Week and see data for Same Period Last Week on Card or Visual.

Thank you

@skydancer,

Welcome to the forum – great to have you here! If you can please post a copy of your PBIX file, that will be a big help in diagnosing the problem. I suspect the issue mail lie with your data model, since I’ve used @JarrettM’s posted code for this date range slicer multiple times in past reports, and never had any problem with it.

  • Brian

Hi @skydancer. Without a PBIX its hard to say, but it sounds to me like you’re trying to access time intelligence calculations that span outside of the range selected in the “period” slicer; if so, the [Dates] table is restricted to the selected period (range) and that may be the root of your issue.
Greg

what about multiple dates? does this require a default relationship?

Thank you :slight_smile: Also happy to be here from now on :))

Here is a pbix.Demo Report for Enterprise DNA.pbix (2.4 MB)

@skydancer,

@Greg was right in his previous post regarding trying to access time intelligence calculations that span out of the selected date range. With a little DAX, you can work around this. Let me know if this works for you:

 Sales LY Test =
CALCULATE (
    SUM ( FactSales[Amount NET] ),
    DATEADD ( 'Date'[Date], -1, YEAR ),
    ALL ( DateRelativePeriods[Period] )
)

Here is a photo of what it looks like:

I have a YouTube video coming out towards the end of this week on the Enterprise DNA YouTube Channel on the Period Table. I’ll make sure I do a follow up video soon on how to handle situations like these with the Period Table.

Thanks
Jarrett

1 Like