Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Average time per status

Need a bit of help.. most likely something super-easy, but I just can't wrap my head around how to do this...   As you can see from below I have a database that logs every time one of our projects ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Try new columns

last status = maxx(filter('Table','Table'[projectid]=EARLIER('Table'[projectid]) && not(ISBLANK('Table'[status])) && 'Table'[Date]<EARLIER('Table'[status])),'Table'[status])

Hour diff = datediff('Table'[statusTime],maxx(FILTER('Table','Table'[projectid]=EARLIER('Table'[projectid]) && ('Table'[status])=EARLIER('Table'[last status])),'Table'[statusTime]),hour)

 

You can change hour to the minute, second, day etc

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Can't seem to get it to work as it's supposed to.

     

    If you look at the picture on the right... I'm looking to show the average hours spent in each status, and knowing that status 10 and 20 are usually the steps that are done quickly and that status 80 is usually what drains time, it seems like the numbers given by your code is wrong somehow.

    Furthermore the result shown for status 80-180 is a negative number... It shouldn't be possible for the time spent on these status steps to be negative.

     

    Any idea as to what im doing wrong?

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Due want to put some time across all step min and max time between all steps divide my total step?

       

      Also, share sample data and sample output. Mark me @

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I'm sorry.. but that just doesn't make any sense to me..?

        I have shared a screenshot of the data and the output im looking for.

         

        Will try to figure this out some other way