How to make slicer default showing latest month as the selection when new data come in every month?

[ssspk]

I have a dashboard which contains a slicer for user to select different month of data, the data will refreshed every day. However, I don't know how to make the slicer automatically pointing to the latest month after each refresh, is this possible to do so in Power BI? I hope end-date always displays based on the data available. But Start date should be always one month before the end date is selected by default when page is loaded. Slicer with setting 'between'. 1/14/2022 8/9/2024 So here From date i have display it one month before the End date(8/9/2024). Is it possible with Slicer with setting 'between'.? Thank you.

[Anonymous · Accepted solution]

Hi @ssspk ,

Thanks SamWiseOwl  for the quick reply. I think SamWiseOwl  offers a great solution.
I have some other ideas for you, we could create a pre-selected slicer that shows the most recent month's date when you click on the Reset button, and you can freely select the date when you click on Clear. But he can't display it in "between" format.

(1) Create a preselected slicer.

(2) We can create tables.

Date = CALENDAR(DATE(2022,1,14),TODAY())

_PreselectedSlicer = DATATABLE(
    "IsDirtySlicer1", BOOLEAN, 
    {
        {FALSE()}, 
        {TRUE()}

    }
)

(3) Create a measure.

_Flag = 
VAR __maxday = MAXX(ALL('Date'),[Date])
VAR __minday = EDATE(__maxday,-1)
VAR __SelectedMonth = SELECTEDVALUE('Date'[Date])
RETURN 
    (__maxday >= __SelectedMonth) && (__minday <= __SelectedMonth) 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous · Accepted solution]

Hi @ssspk ,

Thanks SamWiseOwl  for the quick reply. I think SamWiseOwl  offers a great solution.
I have some other ideas for you, we could create a pre-selected slicer that shows the most recent month's date when you click on the Reset button, and you can freely select the date when you click on Clear. But he can't display it in "between" format.

(1) Create a preselected slicer.

(2) We can create tables.

Date = CALENDAR(DATE(2022,1,14),TODAY())

_PreselectedSlicer = DATATABLE(
    "IsDirtySlicer1", BOOLEAN, 
    {
        {FALSE()}, 
        {TRUE()}

    }
)

(3) Create a measure.

_Flag = 
VAR __maxday = MAXX(ALL('Date'),[Date])
VAR __minday = EDATE(__maxday,-1)
VAR __SelectedMonth = SELECTEDVALUE('Date'[Date])
RETURN 
    (__maxday >= __SelectedMonth) && (__minday <= __SelectedMonth) 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[SamWiseOwl · Accepted solution]

This is awesome, I had no idea it existed! Thank you @Anonymous !

@ssspk if you use this technique set this visual to filter the visible slider:

Then hide the preselected slicer using the selection pane:

And finally add a Clear all slicers button you end up with a slicer thats prefiltered and your users just click clear all slicers to unfilter them both.

[nadia_ab]

Hi SamWiseOwl and @Anonymous ,

I tried to replicate the logic and flow to my own workbook.
I want my dashboard to show the latest available data in my report and it will dynamically changes as months go by.

however, Im not sure why did my Slicer(primary) only show values selected in the Preselect Slicer when i click on reset in this slicer.

I checked on the workbook sent by @Anonymous , seems like it has no problem to show other months in Slicer at the same time some values are checked in the Preselect Slicer (second picture). 

any idea on this?

[SamWiseOwl]

Hi @ssspk 

Could you have a slicer that is filtered to only the top 31 rows, slicing another slicer which has all the rows?

Your user then clears the first slicer which allows the second slicer to have all its values back.

[SamWiseOwl]

HI @ssspk 

Could you apply a TOP N filter to your slicer using the latest date in your column:

Or create a YearMonth column do Top N 1 to return the most reccent value.

[ssspk]

HI @SamWiseOwl ,

Thank you for your reply. By adding the filter for the date it displays only the last 30 days data in screen and from date also changed as like below ,

7/11/2024 and 8/9/2024.

But i have a data Start ing from 1/14/2022 . While page loads From date Should set as 30 days before the to date avilable. Later i cant slide/see the previous dates report. 

Ex: 5/15/2023 to 8/9/2024. This is not possible now.

I dont want the below scenario images, I want above scenario images with default setting of Start date as one month before the end date which is available in the data/screen/to date.

Thank you.