Filter table to only show last entry within period defined in date slicer

[ECE]

Please help !!

I have a data source (lets call it Main Table) containing the following primary columns:

Last Update | Plant | Product | Frame | Lead Time

This data source is linked to a Date Table, a Product table, a frame table and a Plant table.

The 'Last Updated' field is not necessarily the same for all plants.

In my report I have a Data slicer based on my Date Table and I have a table visual with the following columns:

Data Table[Date] | All Plants[Plant] | All Products[Product] | All Frames[Frame] | Main Table[Lead Time]

I need to filter the table visual to only include the latest update rows per plant that is <= the last date in the date slicer.

I am able to make it work, without the date slicer, by creating a calculated column in the main table containing the value 0 or 1. 1 indicating last refresh. I can then add this calculated column to the visual level filter and set it to 1.

It is however not possible to use a calculated column, if I also need to account for the Date Slicer.

My initial thought was to try and accomplish a similar result, just using a measure, but without luck..

Neither the build in filter by topX I have been able to make work.

Any thoughts on how to accomplish this are much appreciated !!

[Greg_Deckler]

So, you should be able to do something like:

Measure Flag = 
VAR __maxDate = MAX('Calendar'[Date]) //max date of date slicer
RETURN
IF(MAX('Data Table'[Date])<=__maxDate,1,0)

Add this to your table and filter on it.

[ECE]

Thanks for your reply Greg.

Unfortunately this does not work.

I only want to display the last update per plant. Your solution is returning all rows (updates) before the max slicer date.

As default the table should display the current delivery situation (latest entry by plant), but by changing the max date of the slice, the user should be able to see how the situation looked like at a given point in time.

[v-shex-msft]

HI @ECE,

Can you please share some sample data and snapshots to help us clarify your scenario?

Regards,

Xiaoxin Sheng

[ECE]

So I finaly found a solution that works - trial and error over and over... and over..

Stated Lead Time Filter = 
    IF( NOT (ISBLANK(MAX('Stated Lead Time'[Calendar Day]))) ;
            RANKX(                        
                FILTER(
                    ALL (   'Stated Lead Time'[Calendar Day];
                            'Stated Lead Time'[Plant]
                        );
                    'Stated Lead Time'[Plant] = MAXX(filter('Stated Lead Time'; 'Stated Lead Time'[Plant] = EARLIER('Stated Lead Time'[Plant]));'Stated Lead Time'[Plant])
                    );
                CALCULATE(MAX('Stated Lead Time'[Calendar Day]))
                )
    ; BLANK())

I am not sure this is the cleanest solution, so I am still happy to hear if anyone has a better solution.

The Data could look like this. Entry date is linked to separate DateTable and product and Frame also linked to separate tables.

If Max date selected using Data Slicer is 2018-16-11 the following table should be shown:

But if the Max date of the Date Slicer was 2018-11-27, the table should instead include the following rows: