cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Danny_Stultiens
Frequent Visitor

Default last date in dropdown slicer with dates

Hi all,

 

My dashboard contains a dropdown slicer with a month value from the calender table to filter the visuals on the dashboard. I would like this dropdown slicer to automatically show the last month that is available in the calendar by default, but previous months must also stay available for the user to select.

 

At this moment, when a new month is added to the calendar table, it becomes available in the dropdown, but it keeps showing the original month as default.

 

I tried a few solutions that are mentioned within different forums, but they are all just a bit different then my case.

 

Can someone point me in the right direction to get this default date value dynamic?

 

Thanks a lot in advance!

 

With kind regards, 

 

Danny

1 ACCEPTED SOLUTION

Ok, found a solution; add a column to the calendar table. In there put a formula "if month column value is previous month, then "previous month" else give month value. Then put this column in the slicer and set default value to "previous month". Seems a long way around, but does the trick.

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Danny_Stultiens , A new column in date table

 

Month Type =
var _max = maxx(allselected(Fact), Fact[Date])
Switch( True(),
eomonth([Date],0) = eomonth(_max,-1),"Last Month" ,
Format([Date],"MMM-YYYY")
)

 

or

 

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

save on last month in slicer

 

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Hi Amitchandak,

 

Thank you for your quick reply. I have no issue to find the last date/month (just with the max function). But my issue is to set the default value on the slicer to the max value in de calendar table.

 

I expect that the answer is in your last reply "save on last month in slicer", but I do not exactly understand what you mean. I would like to prevent me from opening and saving the dashboard to the last month every time there is new data. 

Ok, found a solution; add a column to the calendar table. In there put a formula "if month column value is previous month, then "previous month" else give month value. Then put this column in the slicer and set default value to "previous month". Seems a long way around, but does the trick.

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.