How to calculate these time measures

[DeBIe]

Hello all,

I've got the question to calculate active booking times from bookkeepers that are booking invoices in a specific system.  I will list a copy of my table (ProcessedTable) down below to hopefully be more clear of what data I have and how I can reach my end goal.

CreateDate = is the date/time for an invoice ready to be picked up by bookkeepers (this is not the exact same time for a bookkeeper to start booking invoices)

ExportDate = is the date/time a bookkeeper booked the invoice

InvoiceNumber	CreateDate	ProcessedDate	Bookkeeper
12345	10-7-2020 12:00	10-7-2020 15:00	Karl

What I need to know first is the lead time of the invoice. I will do this with the formula

_LeadTime Invoices = DATEDIFF(ProcessedTable[CreateDate],ProcessedTable[ExportDate],MINUTE)

This will result in the following columns ( I have added more data to get a better picture of my upcoming question/problem:

InvoiceNumber	CreateDate	ProcessedDate	Bookkeeper	LeadTime Invoices
12	10-7-2020 12:00	10-7-2020 12:05	Karl	5
13	10-7-2020 12:00	10-7-2020 12:10	Karl	10
14	9-7-2020 12:00	10-7-2020 12:30	Karl	1470
15	8-7-2020 12:00	10-7-2020 14:00	Karl	3060

The big question is: A manager would like to know in what amount of time Karl booked these 4 invoices. I could calculate the difference between the first booked invoice and the last booked invoice from Karl. That will result in 115 minutes. In theory, a bookkeeper should easily be able to book these 4 (or more) invoices within 10/15/20 minutes. The reason that the Karl did not book these 4 invoices in a shorter amount of time is probably that he got a phone call that kept him busy, he went out for lunch, or for whatever reason.  

Therefore I need some intelligence to be built in that should monitor if there is more then (for example) 15 minutes between a booking, and if so, it will count as inactive booking time or something. I am not sure yet how I will be able to display this. Ideas are welcome.

Hopefully, I have explained myself well, and if someone could help that would be very appreciated. If more info is needed then I will provide more information.

[amitchandak]

@DeBIe ,Not sure I got it. but can column like this help?

Inactive Time = if([LeadTime] >15, [LeadTime]-15,0)
Active time = if([LeadTime] >15, 15,[LeadTime])

[DeBIe]

@amitchandak thank you for your reply. I don't think it will be the answer to my problem.

My end result would be a table with bookkeeper names and the number of invoices they booked in a day/week/month with a booking speed. The CreateDate is the time for an invoice to be available in the system for pick up. Problem is that this is not the start-time of a bookkeeper. It's often possible that the Createdate is a week before it will be booked and gets a ProcessedDate.

I think it's only possible to take the first invoice of a bookkeeper that is being processed. So for example

Bookkeeper	InvoiceNumber	Booked	ProcessedDate
Karl	10	True	14-7-2020 10:00

Bookkeeper	StartBookingTime
Karl	14-7-2020 10:00

From here on there will be more invoices being processed by Karl. I can take the last processed date of the day, but here is my problem again. As I mentioned before I need some intelligence built-in that checks if a bookkeeper takes longer then 15 minutes to book 1 invoice. If that is true, the "timer" for StartBookTime or Active book time has to restart counting again. If Karl would book 10 invoices with the speed of exactly 1 invoice per 10 minutes, then it would be as simple as 100 minutes active book time, because he did not cross the time-limit of 15 minutes per invoice.

[v-lili6-msft]

hi @DeBIe 

Share your sample data and your expected output, that will be a great help.

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

Regards,

Lin

[DeBIe]

hi @v-lili6-msft ,

It's been quite busy, but I'll be posting sample data asap!