cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamiH81
Frequent Visitor

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

Skärmklipp.JPG

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  🙂

 
 
8 REPLIES 8
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Skärmklipp.JPG

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)

 

Screenshot 2020-03-01 00.29.39.png

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

Skärmklipp.JPG

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 @



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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)

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors