Default values for Date Slicer (in between dates)

[jps]

How Can I get the date Slicer to show start Date as “1st January of the current year”, and End Date as “Todays Date” As a default Value, and if a user changes the value, he should still be able to select all other dates
dateslicer.pbix (149.0 KB)

So in the attached File I should have start date to default to 1st January 2022 and End Date to default to “Today’s Date” But all other dates in the table still available for selection

[lbarron20]

dateslicerlb.pbix (149.1 KB)

Here is two ways you can do the slicer, I don’t believe you can mix and match, it would have to be the slider that you can move with regular dates or the date hierarchy to show the date as Friday, January 01, 2021.

[David]

jps I guess it may also be dependant on what you want this for, however, you could consider using the Period Slicer M Code (provided sometime ago on the EDNA pages, will look it up).

But M Code for this is below (hidden of ‘periods’ in the Code below and shown in the attached pbix for your information
dateslicer - DH.pbix (162.5 KB)

---

let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Ranges = {
{“Current YTD”, Date.From(Date.StartOfYear(TodaysDate)), TodaysDate, 1}
// {“Last 3 Months”, Date.AddMonths(TodaysDate, -3), TodaysDate, 2},
// {“Last 6 Months”, Date.AddMonths(TodaysDate, -6), TodaysDate, 3},
// {“Last 9 Months”, Date.AddMonths(TodaysDate, -9), TodaysDate, 4},
// {“Last 12 Months”, Date.AddYears(TodaysDate, -1), TodaysDate, 5},
// {“Last 24 Months”, Date.AddYears(TodaysDate, -2), TodaysDate, 6},
// {“Last 36 Months”, Date.AddYears(TodaysDate, -3), TodaysDate, 7}
},
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

---

Hope this helps?

David Hampson

[David]

Link for the topic as mentioned

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 Cr…

[JarrettM]

@jps ,

@BrianJ and I were just going through a similar situation the other day. We were able to create some mesasures that will automaticall set the date ranges in your slicer, but you are limited to just the dates in that range when adjusting the slicer. Not saying it is impossible to figure out how to adjust the dates outside of the default date range with these measures in place, but probably not a quick and easy solution.

Here are the measures that you could use:

Job Slicer Start Date = 
 DATE( YEAR( TODAY() ), 1, 1  )

Job Slicer End Date = 
TODAY()

Active Slicer Date = 
IF(
    SELECTEDVALUE( 'Date'[Date] ) >= [Job Slicer Start Date] &&
    SELECTEDVALUE( 'Date'[Date] ) <= [Job Slicer End Date],
    1,
    0
)

Then add the Active Slicer Date measure to your slicer, and select the value to be 1.

Here is a picture of what it would look like:

Thanks
Jarrett

[EnterpriseDNA]

Thanks for working on a solution on this post guys

Hello @jps , did the responses above solve your query?

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’

[EnterpriseDNA]

Hi @jps, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.