Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average Time in Status per Occurence

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

 

  

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I would hopefully like to produce something like this.