I’m currently working with an audit table that tracks the implementation statuses of various solutions across different plants and divisions, along with the timestamps for when each status was set.
I have recently implemented a “Before” slicer to allow users to filter the data based on a selected date. In my use case, I want to filter the table by some measure lets say IsMostRecent to display only the latest status change prior to the selected date. This way, the table would show only one row for each combination of solution, plant, and division — the most recent one before the selected date.
I add below the screenshot of what I want to achieve -> only the green rows are visible in the table, as these are the latest entries prior to the selected filter date grouped by division, solution and plant.
I think you have a date table. Do not create relationship between your date table and main table
then create a measure
Measure =
VAR _max =
MAX ( 'date'[Date] )
VAR _max2 =
CALCULATE (
MAX ( 'Table'[Timestamp] ),
FILTER (
ALL ( 'Table' ),
'Table'[division] = MAX ( 'Table'[division] )
&& 'Table'[Timestamp] <= _max
)
)
RETURN
IF ( MAX ( 'Table'[Timestamp] ) = _max2, 1 )
then add this measure to visual filter and set to 1