Hi,
I have a date slicer on the page, if I’d like to automatically select most recent date in the slicer when users open the dashboard, how to set up the DAX or filter? The most recent date can not only be today, but any most recent date the dataset included. For example the date range is 01/01/2018 to 09/10/2021, the slicer will show 09/10/2021 when user open the dashboard. Appreciate any thoughts and ideas.
Thank you!
Hi,
it would be very helpful, if you could provide an example pbix.
However, without further information, I would recommend trying a MAX-function.
Sabine
I also struggled to get a date slicer to default to the most recent date. Once I got it to work, I realized a default value does not work well if you sync the slicer across several pages. For example, if the user selects a date on page 1, then moves to page 2 and selects a different date - the expectation would be that when the user returns to page 1, the date set on page 2 is presented. However, the default value on page 1 sets the slicer back to the default rather than the date selected on page 2.
How have others resolved this behavior?
Hi @chenwei5712,
Welcome on the forum !
One possible way to create a dynamic date slicer is to add a calculated column to the Date Table, which become the date slicer.
The calculated column contains all the dates of the date table, on condition that the dates are equal or smaller then the max date and equal or bigger then the minimum date in different fact tables, like sales, order, purchase; the other dates left blanc.
Dynamic Slicer Date from DateT (calc column) = Var MinDateAllTables = MIN( Sales[Sales Date]) // example the minimum date in fact tables (here only one sales table) Var MaxDateAllTables = MAX( Sales[Sales Date]) // example the maximum date in fact tables (here only one sales table) // for 3 tables possible formula max( MAX( Sales[Sales Date], Purchase[Date]), MAX( Order[Date])) for 3 tables return IF( AND(DateT[Date] >= MinDateAllTables , DateT[Date] <= MaxDateAllTables), DateT[Date], BLANK())
If the date slicer should present one month “above” the max date, +30 could be added after the Max date " MAX( Sales[Sales Date]) + 30 "
Example PBIX with formulas and report attached:
Dynamische time slicer.pbix (107.5 KB)
When using “Synch slicers” (Select a slicer, go to View -->Synch Slicers), date slicers on different pages can be synchronized, where a selection on one page will also available on other pages, presumably that will help you with your question (?)
See below the attached picture, with the (four) yellow boxes ticked for the date slicer , here is the date slicer synchronized for the two pages.
Report with the dynamic date slicer and with “Sync Slicers”
I hope this answers the question(s), if you have further questions or remarks, please let me know.
Kind regards, JW
This great! I will test it out in my project.
I have a question. In the M code… IF( AND(DateT[Date]
why the “AND” ? There is one clause? What is the “AND” adding to? Am I missing something?
Thank you!
D
Hi @DianaWill
it is DAX-code , the And combines two conditions : (DateT[Date] >= MinDateAllTables , DateT[Date] <= MaxDateAllTables), both have to be true to generate the date in the calculated column; (otherwise leave Blank() )
You can scroll the grey bar direct below the formula as stated above (in case you would not aware of it)
Kind regards, JW
Hello @chenwei5712, welcome to the forum. Good to see that you are having progress with your inquiry.
Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum.
Thank you! 
Hi @chenwei5712 , @DianaWill
Regrettable after some testing: the calculated column does only limit the slicer appearance (date selection) dynamically. Initially the slicer does not filter on the dynamically generated selection range.
Without manual selection in and with the slicer, dates outside the dynamic range are still being presented in graphs and visuals.(!)
For example, see below picture, if the dynamic slicer starts at 20 May and ends 4 June, only after selecting the slicer manually to a date within the slicer, the slicer starts working, selecting the right limited dates on the visual(s). If the slicer is not selected manually, date values outside the slicer still appears.
So it appears not a good solution.
Kind regards, JW
Hi @chenwei5712 , @DianaWill
A working solution for a date slicer on the page, that automatically select the most recent date in the slicer when users open the dashboard, is to create a DAX calculated Date table, where the most recent date is determined from a fact table with the most recent date.
PBIX with example solution attached:
Dynamische time slicer v2.pbix (109.4 KB)
Note: when the fact table names change, it needs to be adjusted in the calculated date table as well.
Appendix: DAX code of a calculated Date table
Calculated Date Table2 = Var MinDate1 = Min( Min(SalesT[Sales Date]), Min(PurchaseT[Purchase date])) //min of the different fact tables Var MaxDate1 = Max( MAX(SalesT[Sales Date]), MAX(PurchaseT[Purchase date])) //max of the different fact tables Var AutoDateTable22 = ADDCOLUMNS ( CALENDAR (MinDate1, MaxDate1), //from the fact tables "Year", YEAR ( [Date] ), "QuarterOfYear", FORMAT ( [Date], "Q" ), "MonthOfYear", FORMAT ( [Date], "MM" ), "DateInt", FORMAT ( [Date], "YYYYMMDD" ), "MonthName", FORMAT ( [Date], "mmmm" ), "MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ), "QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ), "DayInWeek", WEEKDAY ( [Date] ), "DayOfWeekName", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "YearMonthSort", year( [Date]) * 100 + MONTH( [Date]), "Month", FORMAT ( [Date], "mmm" )) return AutoDateTable22
Kind regards, JW
Hi @DianaWill, It’s great to know that you are making progress with your query.
Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate.
Best to create a separate thread for your inquiry so it’ll be easier for other users to check the details of your inquiry.
For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum
Hello @chenwei5712, hope you got what you need for your inquiry from the very informative response from @deltaselect 
We’ve noticed that no response has been received from you since September 25th.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @chenwei5712, 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 create a new thread.
