Calculating Latest Status Before a Selected Date

[JRoj]

Hello everyone,

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.

division	plant	solution_id	status	Timestamp	isMostRecent
PS	8dd5db1f	92ba1b9c	Planned	19-06-24 10:47	1
PS	8dd5db1f	92ba1b9c	In use	06-05-24 2:06	0
PS	8dd5db1f	92ba1b9c	In use	06-05-24 2:06	0
PS	8dd5db1f	92ba1b9c	No status	06-05-24 2:05	0
PS	8dd5db1f	92ba1b9c	In use	06-05-24 2:03	0
PS	8dd5db1f	92ba1b9c	Planned	06-05-24 2:02	0
PS	8dd5db1f	92ba1b9c	Planned	06-05-24 2:02	0
PS	8dd5db1f	92ba1b9c	In use	06-02-24 12:41	0
PS	8dd5db1f	92ba1b9c	Planned	01-07-22 11:54	0
PS	8dd5db1f	92ba1b9c	Planned	28-03-22 11:52	0
PS	8dd5db1f	92ba1b9c	No status	11-08-21 10:56	0
PS	8dd5db1f	92ba1b9c	Planned	11-08-21 10:56	0
PS	8dd5db1f	92ba1b9c	No status	27-04-21 13:19	0
ME	8dd512321	92ba1b9c	No status	06-02-24 12:41	1
ME	8dd512321	92ba1b9c	Planned	01-07-22 11:54	0
ME	8dd512321	92ba1b9c	No status	28-03-22 11:52	0

[v-yajiewan-msft · Accepted solution]

Hi @JRoj , dharmendars007, thank you for your prompt reply!

• Create the measure as shown below:

IsMostRecentMeasure = 
VAR SelectedDate = MAX('Date'[Date]) 
VAR CurrentPlant = SELECTEDVALUE('AuditTable'[plant])
VAR CurrentSolution = SELECTEDVALUE('AuditTable'[solution_id])
VAR CurrentDivision = SELECTEDVALUE('AuditTable'[division])

VAR LatestTimestamp = 
    CALCULATE(
        MAX('AuditTable'[Timestamp]),
        FILTER(
            ALL('AuditTable'),
            'AuditTable'[Timestamp] < SelectedDate &&
            'AuditTable'[plant] = CurrentPlant &&
            'AuditTable'[solution_id] = CurrentSolution &&
            'AuditTable'[division] = CurrentDivision
        )
    )

RETURN
IF(
    SELECTEDVALUE('AuditTable'[Timestamp] )= LatestTimestamp,
    1,  
    0   
)

• Then filter the table visual as shown below:

Result for your reference:

Best regards,

Joyce

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

[JRoj]

Thanks @v-yajiewan-msft, this works really fine. I have one more querstion, as I have also other visuals such as Gauge chart, and I would like to filter the whole page by this measure, so the Gauge is also affected by that filter and take only latest values? Is that even possible? I am trying to do something like this:

SumOfCoverageForMostRecentStatus = 
CALCULATE(
    SUM('AuditTable'[coverage]),
    FILTER(
        'AuditTable',
        [IsMostRecentStatus] = 1
    )
)

[v-yajiewan-msft]

Hi @JRoj,

Thank you for your feedback.

As far as I know, measure can not be used in page filter, and we suggest you add the measure to any visuals you want to filter.

[JRoj]

Hi @v-yajiewan-msft.

Yes indeed measure can not be used neither in the page filter, nor in the other measure. But I propagated the whole logic of IsMostRecentMeasure to the other measures and it works.

Thank you very much!

[dharmendars007]

Hello @JRoj , 

Could you please share us the sample data and expected result this will help us to visulize the logic you are mentioning..

Thanks, 

Dharmendar S

[JRoj]

Added below the original post.