Default Silcer Selection in Power BI (latest date)

Question

I have one query can somebody please tell how to show Latest date in a date format

So basically, I want to show the latest date selected by default and if the user wants to select other dates they can change manually but the latest date should be in a date format

Date = IF('Query'[Date] = MAX('Q'[Date]), "Latest Date", FORMAT('Query'[Date], "MM-DD-YYYY"))

Please help to show the latest date in a date format eg: MM-DD-YYYY

Answer 1

First of all, unless you use a static value like "Latest Date" as you have done in the column above, you will not be able to set the slicer to the latest date. However, you can have your report behave as though the latest date were selected if nothing is selected.

Based on your formula, I am assuming that the table Query holds the static latest date of the model and that the table Q is your calendar table.

Create these measures:

Pivot Date = MAX(Query[Date])

Earliest Selected Date = IF(ISFILTERED(Q[Date]),MIN(Q[Date]),[Pivot Date])

Latest Selected Date = IF(ISFILTERED(Q[Date]),MAX(Q[Date]),[Pivot Date])

Now the hard part is you need to write all your measures to use the date range defined by Earliest and Latest Selected instead of just leaving it up to the slicer to control the dates. This allows you to control what's displayed when nothing is selected.

ExampleMeasure = 
var start_date = [Earliest Selected Date]
var end_date = [Latest Selected Date]

return
CALCULATE([Some Measure], removefilters(q[date]), q[date] >= start_date, q[date] <= end_date)

One of the drawbacks of this approach is that you may be depending on your slicer to show the user which dates are selected. To overcome that, add an unobtrusive visual that shows the dates in use. I like to use a Multi Row Card for this and place it in the top right corner of every page. I include these measures: Available From, Available to, Selected From and Selected To. That way viewers know how much data is in the report overall, and they know what's selected at this time. Placing this in the same place on every page in every report is an excellent practice as your viewers will become habituated to consulting this spot for date info.

Finally, I want to point out that you could implement this logic with calculation groups, and that is how I would do it in one of my own reports. Doing so would eliminate the need to modify all the measures you already have, but it would introduce a number of complexities. Calculation Groups have a big learning curve, so you need to be prepared for that journey. The alternative I have suggested will be quicker than getting up to speed on Calculation Groups. However, you should learn how to use them. I highly recommend everything at sqlbi.com on this topic. They have an excellent search tool on the site to find all their content related to Calculation Groups.