How to calculate the most recent date where the value of a column changes

[mjhpbicommunity]

Hi there, 

I'm hoping someone can help with this challenge, which is outside my level of knowledge with power bi.

Goal:

I have a table that provides a daily snapshot of work item IDs.  Each row specifies the "Work Item ID", the "Date", and the "Parent Work Item ID" (this is the parent ID of the work item listed in the first column).  What I need to do is be able to identify the most recent date before a work item's parent ID changed.  This is because I'm trying to measure the most recent date that a work item had it's parent ID changed to "142395".  The challenge is that during the history of a work item, it may have changed parent ID to "142395" more than once, and I need the most recent date that it did this, not the earliest (which I'm currently only able to do).

Example:

• In the example below we have a daily snapshot of work item ID "15600" (I've included just this work item for simplicity, but there are multiple work items in the table that this would need to be done for).
• I'd like to calculate the most recent date that the parent work item ID for this work item changed to "142395".  In the example below this would have been on "09/11/2020" (highlighted in green below), since the day before it was a different parent ID (171304).
• My current solution isn't working, because I have been filtering on parent ID "142395", then grouping by work item ID and returning minimum date.  But in this example, it would give me last date in the table (highlighted in red below), because this work item has previously had parent ID "142395" before being part of another parent ID.  But I'm only interested in the more recent date it moved back into parent ID "142395".

I hope that makes sense in terms of what I'm trying to achieve.  I'd imagine the solution involves starting at the most recent date for each work item, traversing down each day, comparing the parent work item ID to the previous date's parent work item ID, and then returning the date before it changes to something different.  But I just don't know the DAX for this.

Many thanks for any help provided.

[v-jayw-msft · Accepted solution]

Hi @mjhpbicommunity ,

If you want a column, please try the formula below.

You will need to create an index column in Query Editor first.

Column = 
CALCULATE (
    MAX ( 'Table'[parent work item id] ),
    FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)

Column 2 = 
CALCULATE (
    MAX ( 'Table'[date] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[work item id], 'Table'[parent work item id] ),
        'Table'[parent work item id] <> 'Table'[Column]
    )
)

 Result would be shown as below.

Best Regards,

Jay

[DataInsights]

@mjhpbicommunity,

Try this measure:

Change Date = 
VAR vMaxWorkItemDate =
    MAX ( WorkItems[Date] )
VAR vParentWorkItem =
    CALCULATE (
        MAX ( WorkItems[Parent Work Item id] ),
        WorkItems[Date] = vMaxWorkItemDate
    )
VAR vMaxPreviousWorkItemDate =
    CALCULATE (
        MAX ( WorkItems[Date] ),
        WorkItems[Parent Work Item id] <> vParentWorkItem
    )
VAR vResult =
    CALCULATE (
        MIN ( WorkItems[Date] ),
        WorkItems[Date] > vMaxPreviousWorkItemDate
    )
RETURN
    vResult