Calculation of time spent

[itskool]

Hi,

I was wondering if someone could help me with a problem I have been wrecking my head about for days now.

The case is the following:

There is a workflow to request the creation of a new customer: user requests it though a Sharepoint form, the request goes to the executor, they create the new customer and close the request when it is done. The data is logged into a Sharepoint list that I use to create a Power BI report. My task is to measure the time that it takes the executor to create the new customer. Everything sounds easy so far.

The problems start when the executor requests clarifications from the user. The time that the user spends on providing clarifications needs to be substracted from the time that the executor spends on creating the new customer (because if it takes the user two weeks to provide the update, it is not the executor's fault and should not be reflected in their KPIs).

Update requests are logged into a different Sharepoint list.

Data structure looks something like this:

My question is: is there a way to do this calculation in PowerQuery?

My initial idea was to create two separate "duplicate queries", filter one of them for Executor and the other one for User, and then merge them with the Customer creation log, so that all the information for the request is in the same line - then it would be easy to calculate the time.

The problem is that sometimes the executor can request the update more than once, if something was not clear the first time. In this case two lines will appear for the same log when merging the query which will mess up the calculations.

I realize that some kind of grouping might be necessary, but cannot wrap my head around it.

I would greatly appreciate any help or advice.

[BA_Pete]

Hi @itskool ,

It may be simpler to do this in DAX:

Relate creationLog[Request ID] to updateLog[Master ID]

Then a measure, something like this:

_executionDays =
IF(
    ISBLANK(updateLog[Created]),
    DATEDIFF(creationLog[Date Created], creationLog[Date Executed], DAY),
    DATEDIFF(MAX(updateLog[Created]), creationLog[Date Executed], DAY)
)

Pete

[itskool]

Hi,

Thanks for your reply, I did not consider doing it in DAX, maybe it would be easier.

However, it is necessary to take into account not just the time from the receiving of the final update to execution, but also the time from creation to providing the first update, and from receiving the first update to providing the second update.

Do you think there is a way to calculate this in DAX?

[BA_Pete · Accepted solution]

The time from creation to first update should work in a very similar way:

_firstResponseDays =
IF(
    ISBLANK(updateLog[Created]),
    DATEDIFF(creationLog[Date Created], creationLog[Date Updated], DAY),
    DATEDIFF(creationLog[Date Created], MIN(updateLog[Created]), DAY)
)

The timings between to's and fro's in the updateLog table goes beyond this a bit.

I would probably add an index column in PQ that starts at zero to your updateLog table, then merge the table on itself on:

[Master ID] = [Master ID] and [Update ID] = [newIndexColumn]

This will allow you to get a 'from' date and 'to' date between each interaction.

Pete

[itskool]

@BA_Pete Amazing, thank you! It worked!

I modified it a bit for my case - since my IDs are not consequent, I added the indices for each Master ID.

[BA_Pete]

Glad it's worked for you, great news for last thing on a Friday!

I forgot to mention: You could actually sort your updateLog table first by [Master ID], then by [Created], both ascending, then add an Index starting from 1, then another starting from zero, to ensure you have all the info you need in the correct places to do the self-join, but sounds like you've sorted it out yourself.

Have a good weekend!

Pete