How to identify the most recent occurrence of a specific criteria being met

[Anonymous]

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 identify 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 @Anonymous ,

You could achieve the result by create calculated columns as below.

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

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

Result would be shown as below.

Best Regards,
Jay

[Anonymous]

Hi @amitchandak 

Many thanks for your suggestion, I really appreciate the quick response.

I've tried creating and applying these measures within a table to check the result but it doesn't appear to show anything, i.e. the table is empty when I add either measure as a column in the table.

However, the other challenge is (and I should have been clearer in my original post) that ideally I need to try and identify the most recent date a work item changed it's parent work item ID to "142395" within the query editor table (rather than a measure).  This is because I am merging this table with another in query editor.  

In your suggested measures, I also wasn't sure how it would be able to idenitfy the most recent date a work item changed it's parent work item ID specifically to "142395", as this ID isn't referenced in the measures.

If you have any additional suggestions, I'd be really grateful.  

[amitchandak]

@Anonymous , try meausre

meausre =
var _1 = calculate(lastnonblankvalue(Table[Date], max(Table[parent work item id])))
return
calculate(min(Table[date]), filter(Table, Table[Date] = _1))

or

meausre =
var _1 = calculate(lastnonblankvalue(Table[Date], max(Table[parent work item id])))
return
calculate(min(Table[date]), filter(Table, Table[Date] = _1), allexcept(Table, Table[Work item Id]))