Needs a calculation and visual for inventory items

Hi Jacqee 

Thank you for reaching out to Microsoft Fabric Community Forum.

I recently ran into a requirement where I needed to identify, for each item/ID, the first time a case was marked as Closed after the last time it was marked Open or In Progress. This is part of a case lifecycle/inventory tracking scenario. After experimenting with different approaches, I was able to solve it using two calculated columns in DAX. Here's a breakdown of the logic.

For each ID, return only the first Closed status that comes after the latest Open or In Progress status.

Here's what the result looks like in a table visual. You can see the correct row is flagged with a ✔  for each ID — this is the row that meets the condition.

• Column 1 (LastNonClosedDate) : Gets the last non-closed date for each ID
• Column 2 (FirstClosedAfterNonClosed) : Finds the earliest Closed status after that date
• Only that row is flagged

Attached snip shows the final table where the correct Closed row is flagged with a ✔ for each ID.

Create a caluculated colum to achive the above Solution.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Akhil.

Hi Jacqee ,

I think you can try to create a measure and then use conditional formatting to achieve your goal.

Meausre:

MEASURE = 
VAR _LastProgressorOpen =
    CALCULATE (
        MAX ( 'Table'[status Timestamp] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Status Catg.] in {"Open","In Progress"}
        )
    )
VAR _Close =
    CALCULATE (
        MIN ( 'Table'[status Timestamp] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Status Catg.] = "Closed"
                && 'Table'[status Timestamp] >= _LastProgressorOpen
        )
    )
RETURN
    IF ( MAX ( 'Table'[status Timestamp] ) = _Close, "Yellow" )

Conditional formatting:

Result is as below.

Best Regards.

Rico Zhou