Filter Table based on MAX of date slicer

[AFinster]

Hi all,

I need help with a sticky problem...

I have a date table that is broken in to "week ending" dates, and a data table that has records added according to the week ending date.

I am using a date slicer configured as "Before" so that the user has only 1 date to manipulate. I would like the table showing the data to filter on the maximum week ending date selected in the slicer.

I have also created a measure to display the appropriate week ending based on the date selected.

My data looks like this:

Date Table  

Data Table

My Date slicer with Measure showing MAX(Date) looks like this:

And if it's adjusted, it looks like this

If the slicer is set to the largest date, then I only want the highlighted records to show:

Whereas if the earlier date is selected, only these highlighted records should show:

Rationale for using the date slicer in this format:

I currently use a date slicer as a single-select dropdown, and this works fine, but the date table records expand week by week, as does the data table records, and to get this to reflect the current (max) week, I have to manually set the new filter selection and then re-publish the report every week. Using the date range slicer configured in "Before" mode, I can add date records to the date table and it will constantly default to the largest date value when the data is refreshed, which saves the manual step and allows me to update the database and automate the refresh without opening the report in the desktop.

The help I need:

• Allow the date slicer to gather the appropriate week ending value, and use it to filter the recods based on the latest / largest / MAX week ending value
• OR, demonstrate how I can automatically update the single select date slicer so that it always defaults to the latest / largest / MAX date
• Sample data & pbix files can be added if requested.

Thanks in advance...

Adam

[Nayan_surya]

Hi @AFinster ,

I have faced similar problem in past where I was asked to add max date as default feature, since client wanted to get the latest data. Not only this they had quite a few requirements in terms of presets for the Date slicer which does not come in Native slicer. What we ended up doing is we bought a custom visual named Date picker by Powerviz.

It had all the features they required so that was a life saver.

Here is a Screenshot from the visual.

I think you should check them out.

Here is a link if you want to check this visual - https://appsource.microsoft.com/en-us/product/powerbivisuals/truvizinc1674781244292.date-picker-by-p...  (I believe they offer a free version too)

[AFinster]

Hi all,

I accepted Rico's post, as it was the closest to the solution I discovered independently, with some inspiration from BI Elite (https://youtu.be/AZAL-QPn5Zc ).

My Measures look like this:

MaxInsightDate = MAX(WeekData[InsightDate])

and 

MeasureFilter =

   VAR MaxValue = MAX('Weekly Insights'[Week Ending])

   VAR CurrentMeasureValue = [MaxInsightDate]

RETURN

   IF(CurrentMeasureValue = MaxValue, 1, 0)

and then applied the MeasureFilter to the list of filters for the visual that had to be filtered "MeasureFilter = 1"

Then by using the Before-based date slicer pointed to my date table, I can filter the results accordingly.

Thanks for all the help!

Adam

[AFinster]

Quick update,

Both sample measures created an error regarding a circular reference using:

Measure =

VAR _weekEnd = MAXX(all(Sheet1),Sheet1[InsightDate])

RETURN

CALCULATE([Measure], 'Weekly Insights'[Week Ending] = _weekEnd)

@AllisonKennedy  I also tried the relative date slicer, but could not see how to have the user select a specific date ...

Links to sample pbix and source excel data if this helps:

Link to pbix 

Link to Excel data 

[v-rzhou-msft · Accepted solution]

Hi @AFinster 

You build a measure and drag the measure into filters and show values as 1 to achieve the goal.

You may try may way to calculate WeekEnding directly by measure.

WeekEnding = 
VAR _Maxslicer =
    MAX ( Sheet1[InsightDate] )
VAR _MAXWEEKEND =
    MAXX (
        FILTER (
            ALL ( 'Weekly Insights' ),
            'Weekly Insights'[Week Ending] <= _Maxslicer
        ),
        'Weekly Insights'[Week Ending]
    )
RETURN
    IF (
        MAX ( 'Weekly Insights'[Week Ending] ) = _MAXWEEKEND,
        _MAXWEEKEND,
        BLANK ()
    )

Result:

You can download the pbix file from this link:  Filter Table based on MAX of date slicer

Best Regards,

Rico Zhou

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

[amitchandak]

@AFinster , based on what I got

To make it work best create week start and end in your calendar. Seem like you have friday to Thrusday calendar

The calendar is aatached after signature

or refer by blog for any other day week https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

you can week like

measure =
var _max = date(allselected(Date), Date[Week end Date])
return
calculate([measure], filter(all(date), Date[Week end Date] =_max))

[AllisonKennedy]

Hi Adam~

Assuming that your date table has multiple columns, not just Insight Date?

You should be able to use variables to define the WeekEnd date in each measure, something like;
Measure =
VAR _weekEnd = MAXX(ALL(DimDate), DimDate[Insight])
RETURN
CALCULATE([Measure], Data[Week Ending] = _weekEnd)

You could also try using the Relative date slicer to show last 7 days as default if that would help?