Power BI bar chart to visualize the time taken for a record to move from one status to another

[Chaitanya25]

I have an application with a SharePoint list as a data source. It has status as a choice field in it, which has different kinds of statuses. I used automated flow to change the status of submissions. 
Scenario:
I submit an employee named John as a new employee, the status shows as NEW and the HR department starts working on his contract status changes to HR Department is working on preparing the contracts document. once they are done. the status changes to the orientation in process. Once orientation is done, the status changes to orientation is completed. and last onboarded. 

Each employee undergoes the same process, with the status new onboarded.

i was trying to build a Power BI bar chart that shows the number of days taken for a record to move from different sets of status. like no of days the persons status is new, no of days the persons status is Orintation in process, so on... so that i can get the time taken by each department to complete the employee onboarding process and how much duration taken. 

[Chaitanya25]

Any updates or suggestions on this?

[Chaitanya25]

Thank you for your input, @christinepayton.  I appreciate your suggestion regarding tracking the amount of time taken for the employee onboarding process. However, I'm looking to calculate the duration that each individual employee spends in each status, such as 'NEW,' 'HR Department is working,' 'Orientation in process,' and so on. This would allow me to analyze the time taken by each department for the entire onboarding process. I understand your point about adding a 'close date' for the total process completion, but I'm specifically interested in monitoring the time spent in each status transition for each employee. 

[christinepayton]

Yes, so you would do the same process but you would add a date column for each transition you're tracking. Or snapshot the data on a periodic basis and report on that (snapshotting will give you a picture of which items were in which status on which dates). 

[Chaitanya25]

Right now, I was trying to add a date column to track each transaction. Basically, I use automate flows each time to change the status from one to another. I can save the date in each column to use it to create the reports. but if there are too many statuses, then it would become a problem to have too many columns.

[christinepayton]

You could also do a Power Automate flow to log the status change every time one occurs in a separate list, then report on that. 

[Chaitanya25]

I am able to get the time stamps for each transaction. 

The main problem I face is when using Power BI to develop reports, what parameters should be used.
Suppose, if I have 20 records in a list and want to find the average days it took for each department, or if I select a particular record then show a graph.

Expected output showing average days taken by each department:

Expected output showing no of days taken for record selected:

[christinepayton]

When you put it in the chart, you click the field in the values well to choose what it's summarized by. You can choose average as an option if it's a numeric field. Or create a measure using AVERAGE() DAX.

[Chaitanya25 · Accepted solution]

Thanks for your assistance. I figured it out.

I already captured the dates using the automated flow. In order to find the difference, I created new columns in the Power BI table to calculate the difference between the status change dates. 

This is the formula used to find the difference between dates:(the same way I did for each department)

RecruiterDateDifference = DATEDIFF('Details'[Created].[Date], 'Details'[RecruiterDate].[Date], DAY)

Use the calculated columns to aggregate the duration values based on the status. You can use measures like AVERAGE or MAX to get the desired duration for each status.

finally got the output that i need.

[christinepayton]

If you need to track the amount of time for every single status change from SharePoint, that is a difficult ask. Picking one, such as how long it takes to complete in total, would be pretty simple - you would just add a date field for the "close date" to the item, then have a Power Automate flow that triggers when modified set the field when the status changes to complete. I have been meaning to make a tutorial on this - will see if I can get to that this week unless someone has a handly link to something. 

Then in your reporting, you can simply subtract the created date from the close date to get the duration of the ticket.

Trying to track every single status change duration, you'd either have to use Power Automate to snapshot the data into somewhat of a history and use that in reporting, or add fields and flows for every single one of the status changes you want to track. I did a video on snapshotting SP lists here (https://youtu.be/RBx-HbVpWTQ), but this technique is not going to be great in this particular case because you'd have to be doing daily snapshots, and the size of the datasource would get too large to be using the SP folder connector effectively... it'd be better for something like checking how many were in what status by week or month. You could send the snapshots to SQL instead, but that'll be a premium license. 

Edit: I finished a tutorial on how to set a completion date field with Power Automate and calculate days to complete he....