Average time per status

[Anonymous]

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 changes status.

So for instance projectID 54298 was in Status 30 at 11:22:40 on the 22/2-2020 and changed to status 40 at 13:33:18

But what I want is a calculation of the average time for each status level that I can use to track where we can improve.

So for instance... To see that the average time spent on status 30 (for all projectID) is XX amount of hours, and also track how this average has gone up/down over time.

As I wrote... most likely something very easy to fix, but I just can't seem to figure it out.

So would gladly appreciate any help in this regards.

Thanks  🙂

[Greg_Deckler]

See if @amitchandak 's solution works. If not, please post sample data as text. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Well... still don't get it to work... most likely just me that is overthinking it.

The data that I supplied earlier is as clean as it gets, but it is impossible for me to post all 2,8 million rows of data.

I have uploaded the file to my GoogleDrive, and you are welcome to download the pfix file and take a look: statuslog.pbix 

Below is a screenshot of the rawdata.

[amitchandak]

Check. There was an error in the column name

last status = maxx(filter('statusLogs','statusLogs'[projectID]=EARLIER('statusLogs'[projectID]) && 'statusLogs'[status]<EARLIER('statusLogs'[status])),'statusLogs'[status])

Hour diff = datediff(maxx(FILTER('statusLogs','statusLogs'[projectID]=EARLIER('statusLogs'[projectID]) && ('statusLogs'[status])=EARLIER('statusLogs'[last status])),'statusLogs'[statusTime]),'statusLogs'[statusTime],HOUR)

[amitchandak]

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]

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]

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]

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

[amitchandak]

My assumption in the last calculation was step 10 comes before step 20 and so on. The way is I just look at a time lower than that step

Hour diff = datediff('Table'[statusTime],maxx(filter('Table','Table'[projectid]=EARLIER('Table'[projectid]) && not(ISBLANK('Table'[status])) && 'Table'[Date]<EARLIER('Table'[statusTime])),'Table'[statusTime]),hour)