I have a table of data from Azure DevOps that contains a sequence of status changes for work items. Each record contains an ID for the work item, a status, a date at which the status changed and a date on which the work item was created.
I am attempting to calculate SLA compliance by measuring the number of days between a work item being created and then subsequently reaching one of a set of potential status values. I then need to check if the number of days is more or less than 7.
In my table, I have already added a calculated column that holds the number of days between the created date and the status change date for each record.
I am now trying to add a further calculated column to identify the first occurrence of each work item ID reaching one of a specific set of status values. I then want to flag those records as being used for the SLA calculation.
Please can anyone assist me with the DAX code for finding the first occurrence of one of a set of statuses for each ID in the table? The image below shows a sample of the data.
Thanks in advance.
3
is this what you want
Column2=
MAXX (
FILTER (
'Table',
'Table'[Work Item Id] = EARLIER ( 'Table'[Work Item Id] )
&& 'Table'[Status Change Date] = 'Table'[Created Date]
),
'Table'[Status]
)
2