How to display dashboard refresh date into filter dropdown of date column to pick automatically?

[Sampathkumar_v]

Hi Folks,

Could you please help to find a dax code to disply in date filter dropdown to display dashbaord latest refresh date?

e.g - (I am refreshing my report weekly once on Friday the date filter dropdown should display friday's date only below are the e.g

last friday 02-08-2024 I refreshed the report my filter option is single selection  in date filter to display 02-08-2024 when next refresh will happen 09-08-2024 the filter dropdown should pick the 09-aug-2024 instead of 02-08-2024 automatically.

[danextian · Accepted solution]

Hi @Sampathkumar_v,

This might be what you need. Further reading can be found on the video description:

https://www.youtube.com/watch?v=MrEAZREQuXM 

Please note that if the slicer is a dropdown, the value selected will show what was previously selected but actually already filters to th current/latest date.

[v-jtian-msft · Accepted solution]

Hello,danextian ,Kaviraj11 .thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hi,@Sampathkumar_v, I am glad to help you.

The suggestions of others are great and I would like to make some appropriate additions to their suggestions.

Hi, you want to realize that the slicer defaults to filtering out and displaying the data with the latest refresh date when no data is selected by the slicer, and displaying the data selected by the slicer by default when the slicer selects it.
If my understanding is correct, you can refer to my test below
I've created a measure and a separate slicer table to fulfill your requirement, and the slicer supports multiple selections.
Here are the results of my tests.
1. When the slicer does not select anything, the data with the latest refresh date is filtered by default and displayed

2. When the slicer is normally selected, it performs the slicer's default filtering function, and supports multiple selection

Here is my test. If my test can bring you good ideas, you can refer to it:
1. According to the original data table to create a separate slicer table, do not create a relationship, to avoid the slicer produces direct filtering (because the slicer does not select then any value by default when the full selection)

Slicer form FridayValues.

1. When the slicer is not in the filtering state, the default choice is to display the latest data

M_getdate = 
VAR _LatestDate=CALCULATE(MAX('FridayDates'[Date]),ALL(FridayDates))
VAR _selectSlicer=MAX('FridayDates'[Date])
RETURN
IF(ISFILTERED('FridayDates'[Date])=BLANK(),_LatestDate,_selectSlicer
)




M_notFilter = 
VAR _dateValue=MAX('Table'[Date])
RETURN
    IF(_dateValue=[M_getdate],1,0)

2. When the slicer is in the filter, all the options selected on the slicer are displayed

M_Filter = 
VAR _date=SELECTEDVALUE ('Table'[Date])
VAR _slicerTable=
   CALCULATETABLE (
        VALUES ( 'FridayDates'[Date]),
        FILTER ( ALLSELECTED ( 'FridayDates'[Date] ), 'FridayDates'[Date] = _date )
    )
RETURN
IF(_date IN _slicerTable,1,0)

3. Use a final judgment measure as a condition for data filtering, set the filter to display only the measure Result = 1

M_test = IF(ISFILTERED(FridayDates[Date])=BLANK(),[M_notFilter],[M_filter])

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[Sampathkumar_v]

Hi @v-jtian-msft 

still I am working on your suggestion will keep update you if needs further. thanks

[danextian · Accepted solution]

Hi @Sampathkumar_v,

This might be what you need. Further reading can be found on the video description:

https://www.youtube.com/watch?v=MrEAZREQuXM 

Please note that if the slicer is a dropdown, the value selected will show what was previously selected but actually already filters to th current/latest date.

[Kaviraj11]

Re: How to show latest date as default selection i... - Microsoft Fabric Community