Get max date (max of a field) in Slicer as default value

[dpombal]

Hi all,

I have a fairly simple dataset (simulating a real case)

Table includes several snaphots of the same data, using Date_of_Update

 

So for consuming this table , selecting a Date of Update is mandatory (Selecting full table makes no sense)

I should require , a slicer with Date update, having as default value, the max of Date of Update

How to predefine a slicer with the MAX of a field , or a page filter?

Regards

[Anonymous · Accepted solution]

You can add a calculated column to your Date table:

IsLastDate = Date[Date] = MAX(Table[Date_Of_Update])

The value will be TRUE for the last Date_Of_Update and FALSE for the rest.

Then you can use this column as a report level filter

[Chihiro]

Other than saving PBIX with max date selected. There is currently no function for slicer to auto select some value.

Alternatives I've used in the past is to create Measures/Visuals using FILTER() function to show data for latest date.

Then have it as default view. And have shape with link to bookmark to show Visuals for entire dataset (and hide initial visuals for latest date), if user clicks on it.

[dpombal]

This solution is very costful in a model with 150 measures, appart from that, take into account entire dataset makes no sense, you should always filter 1 date of update, aggregating all is not useful

Then have it as default view. And have shape with link to bookmark to show Visuals for entire dataset (and hide initial visuals for latest date), if user clicks on it.

I was thinking on something like  calculated column...and using it as page filter...but not sure how it works with a slicer....any idea?

[Chihiro]

I would consider splitting model into two then.

One for small segment with fine grain (i.e. MAX([Date])).

Then another for whatever other level you currently have.

Alternately, as have been mentioned, use calculated column, use that as Page level filter criteria.

Then again use bookmark to flip between the two (or use filter pane).

[Anonymous · Accepted solution]

You can add a calculated column to your Date table:

IsLastDate = Date[Date] = MAX(Table[Date_Of_Update])

The value will be TRUE for the last Date_Of_Update and FALSE for the rest.

Then you can use this column as a report level filter

[dpombal]

How will this report level filter collide with An slicer? if it is filtered by a report level, I think a slicer will not be effective

[v-yuezhe-msft · Accepted solution]

@dpombal,

It depends on your requirement. You can create a slicer using the IsLastDate calculated column instead of dragging the calculated column into report level filter. Report level filter will filter slicers and visuals in all report pages 

Regards,
Lydia

[Greg_Deckler]

Well, you could select the max date and save the PBIX.

[dpombal]

This solution is not valid for me, imagine tomorrow a latest date appears in the dataset , thanks