Average Time in Status per Occurence

[DAXtheDestroyer]

I am trying to figure out how to show the average time a certain project is in a particular status, and a separate table that does the average time an issue will slow a project. 

Ex 1: Status Change

Here is a good example of a project, identified by the initiative key and issue_id, showing a change in status. I'm not too particularly concerned with the time the particular project was in the status, but rather the average time projects, in general, will be in a red, yellow, or green status.

Like this:

Example 2: Issue Change

Here I have a project that stays in a red status, but has different issues plaguing it throughout that period. I'd like to do a similar thing and measure how long particular issues will slow projects.

Like this:

These are the issue categories:

I don't think the measure has to be driven by the "Update Date," because each entry is its own week. I only update the source once a week, creating another row for each IP. Maybe it just needs to be a count function that my brain just hasn't produced on its own.

Any help is appreciated. Thank you.

[v-yangliu-msft · Accepted solution]

Hi  @DAXtheDestroyer ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Flag =
var _lastcolumn=CALCULATE(MAX('Table'[Issue Category Short]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
return
IF('Table'[Issue Category Short]=_lastcolumn,0,1)

Group =
CALCULATE(SUM('Table'[Flag]),FILTER('Table',[Index]<=EARLIER('Table'[Index])))

Day =
DATEDIFF('Table'[Update Date],'Table'[MaxDate],DAY)

3. Create measure.

Measure =
var _count=
COUNTX(FILTER(ALL('Table'),
'Table'[R/Y/G]=MAX('Table'[R/Y/G])&&'Table'[Group]=MAX('Table'[Group])),[Index])
var _day=
SUMX(FILTER(ALL('Table'),
'Table'[R/Y/G]=MAX('Table'[R/Y/G])&&'Table'[Group]=MAX('Table'[Group])),[Day])
return
DIVIDE(
    _day,_count)

4. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[amitchandak]

@DAXtheDestroyer ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[DAXtheDestroyer]

Here is a longer shot at the data:

I am trying to produce two outputs for each column in red. In the "Issue Category Short" column, that I have provided the list of options in my initial post, I would need the average time any "Initiative Key" has had that issue. For example, Initiative IP-6 had a period of 4 weeks of "Dependencies," 2 weeks of "Commitment or TDD" and then 2 weeks of "Dependencies" again. I would like to consider them to be 3 different entries, 2 for Dependencies and 1 for Commitment. Would I be able to get an average time these issues last? and the same for the R/Y/G column?

This is a table that gets appended weekly and each Initiative generates a new row for the week along with a new issue if applicable and R/Y/G status. 

I would like the output to be tabulated like: 

 and 

I can DM my pbix if it is necessary.

[DAXtheDestroyer]

I would hopefully like to produce something like this.