Calculate working days/ hours spent on each level in a process

[amuola]

Hello,

Could anyone help me on this:

I would like to calculate the number of working days/ hours between each level and ID in a certain process. The table below shows an example of the case; for each ID, what is the number of days or hours (working, excluding weekends and holidays) spent on each stage?

To exemplify: for ID nr 101, the process starts when the Manager receive a certain form from an employee at 29.09.2016 11:25, he do some work on it and send it to the HR Department 01.10.2016 09:07, the HR Dep. send it to the Shared Service Center (SSC) at 07.10.2016 10:38 and the SSC do the finale job and Transfer/ close the case at 09.10.2016 11:17. My question; how to calculate the working time spent on each level in the process for each of the ID? Each ID could start at different levels (Employee, Manager, HR Dep. or SSC) but always ends with TRANS.

ID	INBOXID	DTTIMESTAMP	Time spent on each level
101	Manager	29.09.2016 11:25	?
101	HR dep	01.10.2016 09:07	?
101	SSC	07.10.2016 10:38	?
101	TRANS	09.10.2016 11:17	?
102	Employee	13.10.2016 13:22	?
102	SSC	13.10.2016 14:38	?
102	On hold	14.10.2016 09:08	?
102	TRANS	21.10.2016 14:01	?
103	SSC	26.10.2016 12:13	?
103	TRANS	26.10.2016 12:29	?
104	HR dep	24.10.2016 12:01	?
104	SSC	25.10.2016 08:02	?
104	TRANS	25.10.2016 08:03	?
105	SSC	21.10.2016 15:36	?
105	TRANS	24.10.2016 13:35	?
106	HR dep	24.10.2016 11:45	?
106	SSC	25.10.2016 08:01	?
106	TRANS	25.10.2016 10:09	?

[Anonymous]

Hi @amuola,

You can refer to below steps to get the diff time of nearest states which has the same "ID".

Source table:

Load to query editor and add a column to format the date.

Formula:

Table.AddColumn(#"Promoted Headers", "USDate", each DateTime.From([DTTIMESTAMP],"UK"))

Change type:

Save and return to report view, then add a measure to calculate the diff.

Diff =
var currDate=MAX([USDate])
var lastofCurrDate=MAXX(FILTER(ALL(Sheet12),[ID]=MAX([ID])&&[USDate]<currDate),[USDate])
return
if(lastofCurrDate<>BLANK(),DATEDIFF(lastofCurrDate,currDate,HOUR),0)

Create a visual to display the result.

Regards,

Xiaoxin Sheng

[amuola]

Hi,

that is fantastic, thank you!

One more question; do you know how to displace the result one level? If you look at the visual you made, there should not be any time spent on the TRANS level - so every result is displaced. As it is now, the sum for TRANS should be the sum for SSC, the sum for SSC should be the sum for HR dep. and so on.

Regards

Amund

[ImkeF]

Please check out this file: https://www.dropbox.com/s/vnrusty447ysjrz/DurationExclV2_Shift.pbix?dl=0

& come back if this was not what you wanted.

[amuola]

Hi,

I have been working on this problem now and I have two questions:

- You have made en unique Key figure by merging ID and INBOXID. What is happening quite often is that the form is returned one or more levels back due to incompleteness. In that case, there will not be a unique number to relate in the data model - the same ID number will be connected to each level more than once. E.g. you will have ID 101 at least two times at the HR dep., as a result the Key figure 101HR dep. will appear two or more times, and hence it is not possible to relate the tables in the model (many to many relationships). Do you have any suggestion how to solve that? In addition, it is necessary to keep the ID and the INBOXID as separate columns in order to be able to e.g. calculate the sum of time spent at the HR dep., regardless of the ID

- The second question is the amount of data loaded when applying the data to the model; the initial Excel file (a test) is less than 1 MB, however when loading to data model, it generates 30 MB of data. The files that is loaded in to the model is typically 300 MB, and it takes hours to update. I have not been able to figure out why this is happening, do you know?

Regards

Amund

[ImkeF]

Hi Amund,

1st question:

No problem in creating the Key-column and mainting the original ones. Just replace the last step with this formula:

Table.AddColumn(#"Changed Type", "Key", each Text.Combine({Text.From([ID], "de-DE"), Text.From([INBOXID], "de-DE")}, ""), type text)

If there is no key, the solution will not work. So I'd recommend to delete all rows that doesn't contain the necessary infos.

Re the file-size, I ran into this trap: https://blog.crossjoin.co.uk/2016/12/16/power-bi-model-size-bloat-and-auto-datetime-tables/

You need to adjust the settings as described in that article. 

[amuola]

Hi,

with regards to the Key column, it does not help much, since there is still no column with unique values (Key) due to the returns and the process start all over again with the same ID number, combining the same ID and INBOXID several times. It is not possible to delete any of the duplicated values in the DateRecordings Table; these are steps that needs to be counted when summing up total time spent in the process.

I tried to turn of the Auto Date/ Time, however I got an error message in the DatesDurations table saying “Column reference to 'Start' in table 'DateRecordings' cannot be used with a variation 'Date' because it does not have any”.

Any suggestions…?

Regards

Amund

[ImkeF]

Hi Amund,

re the error message: Sorry, some adustments in the code for the calculated table had also been made.

You find the correct file here: https://www.dropbox.com/s/3ax9u9boh7i39fd/PBI_DurationExclV3_Shift.pbix?dl=0

Re your other request: I might not have gotten around it really, so please post some sample data of the desired in- & output.

Thank you.

[amuola]

Hi Imke,

See the below table, with an extended ID 101. The fourth line shows a return to Manager (RManager), and then the same form goes back to HR dep and finally SSC before it get TRANS. This means that total time spent at the manager level is (row 2 – row 1) + (row 5 – row 4), total time spent at the HR dep. level is (row 3 – row 2) + (row 6 – row 5) etc. There will quite often not be a single value for the combination 101 HR dep, or 101 SSC etc. What I would like to see is:

• Total time spent on ID 101
• As well as total time spent at each level within ID 101

The level TRANS is as before, just the final step and no time is spent on that level. So row 7 minus 6 is time spent on the SSC, not TRANS

Row	ID	INBOXID	DTTIMESTAMP
1	101	Manager	29.09.2016 11:25
2	101	HR dep	01.10.2016 09:07
3	101	SSC	07.10.2016 10:38
4	101	RManager	10.10.2016 11:25
5	101	HR dep	12.10.2016 14:24
6	101	SSC	15.10.2016 09:08
7	101	TRANS	17.10.2016 10:19
8	102	Employee	13.10.2016 13:22
9	102	SSC	13.10.2016 14:38
10	102	TRANS	21.10.2016 14:01

I hope this clarifies the issue … J

Regards

Amund

[ImkeF]

Sorry, I'm struggling to pull this all together.

Could you please post a table with the desired output?

[amuola]

Hi,

The preferred output is something like this, where you can see total time spent on each case as well as time spent within each ID, average time spent on ID etc.

 

Regards

Amund

[ImkeF]

Sorry, my questionn was refering to what the correct numbers from your example with the new calculation logic would be.

[amuola]

Hi,

I'm not quite sure what you need, is it the number of days within each level from the example table? Could you please specify a little more?

Regards

Amund

[ImkeF]

Yes, the correct duration numbers on the desired aggregation level was what I meant.

Have a look at the file here: https://www.dropbox.com/s/e70z2ymr2ux7kba/PBI_DurationExclV5_Shift.pbix?dl=0

This looks pretty much of what you're after in my eyes 😉

[amuola]

Hi

Thank you, now it works pretty much as I wanted! However, I have two issues

- I had to change the calendar from building it in power query to make a calendar in DAX (CALENDARAUTO()) and create a regular relationship. The reason was the time it took to update with more data, it never ended. Now it works fine.

- The main problem now is the amount of data generated when loading the queries into the data model. The size of the Excel file I use for testing is 56 kb (approx. 1 000 rows). When loading this into the model, it generates 58 Mb of data – even though I have turned off the Time Intelligence functionality (under Options). The amount of data we typically would need to load into Power BI is 120 Mb or 1, 5 -2 million rows. I just tried one regular file (contains data for one month), 6 Mb/ 120 000 rows and loading the data to the model it never ends, I stopped it after 1 GB …

Do you have any idea why this is happening? Could there be any other functionality that creates hidden columns/ tables, making the amount of data so enormous?

Regards,

Amund

[ImkeF]

Thats really strange. Pls check out this: http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

[amuola]

Hi,

I have deleted each step in order to see when the amount of data increases and these are the steps that generates a lot of data:

= Table.AddColumn(AddIndex, "Start", each if [ID]=AddIndex[ID]{[Index]+1} then [DTTIMESTAMP] else "")

= Table.AddColumn(#"Removed Errors1", "End", each if [Start]<>"" then AddIndex[DTTIMESTAMP] {[Index]+1} else ""),

Do you have any idea how this could be created in another way in order to get the desired output without generating all the data?

Regards

Amund

[ImkeF]

Well, we're duplicating datetime-columns here. They deliver many unique values and that's not what the vertipaq engine likes.

You can try to find a way splitting them up into date-& time-part but I cannot oversee at the moment if this would work.

Re the load-time, this changed code for the query "DateRecordings" should speed it up:

let
function = (partition) =>
let
    Source = partition,
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddIndex, "Start", each if [ID]=AddIndex[ID]{[Index]+1} then [DTTIMESTAMP] else ""),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Added Custom", {"Start"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Errors1", "End", each if [Start]<>"" then AddIndex[DTTIMESTAMP]{[Index]+1} else ""),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Start] <> "")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"DTTIMESTAMP", type datetime}, {"Start", type datetime}, {"End", type datetime}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Key", each Text.Combine({Text.From([ID], "de-DE"), Text.From([INBOXID], "de-DE")}, ""), type text),
    #"Added Custom2" = Table.AddColumn(#"Inserted Merged Column", "StartDate", each Date.From([Start])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndDate", each Date.From([End])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"StartDate", type date}, {"EndDate", type date}})
in
    #"Changed Type1",

Source = Source,
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Partition", each _, type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"ID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "CallFunction", each function([Partition])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Partition"}),
    #"Expanded CallFunction" = Table.ExpandTableColumn(#"Removed Columns1", "CallFunction", {"ID", "INBOXID", "DTTIMESTAMP", "Index", "Start", "End", "Key", "StartDate", "EndDate"}, {"ID", "INBOXID", "DTTIMESTAMP", "Index", "Start", "End", "Key", "StartDate", "EndDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded CallFunction",{{"Start", type datetime}, {"End", type datetime}, {"StartDate", type date}, {"EndDate", type date}, {"DTTIMESTAMP", type datetime}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type",{"DTTIMESTAMP"})
in
#"Removed Columns2"

[amuola]

Thank you. However, when I try this on my real data I got an error saying "Expression.Error: The import Source matches no exports. Did you miss a module reference?" and then "Expression.Error: The name 'Source' wasn't recognized.  Make sure it's spelled correctly." My source is now a folder and starts with "Source = Folder.Files("P:\1..." I am not that in to the M language and the error messages are a bit hard to understand what actions to take.... Any suggestions?

Regards

Amund

[ImkeF]

This should be the same like your previous version of your query:

Source = NameOfTheQueryThatHasPreviouslyBeenThere:

let
function = (partition) =>
let
    Source = partition,
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddIndex, "Start", each if [ID]=AddIndex[ID]{[Index]+1} then [DTTIMESTAMP] else ""),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Added Custom", {"Start"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Errors1", "End", each if [Start]<>"" then AddIndex[DTTIMESTAMP]{[Index]+1} else ""),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Start] <> "")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"DTTIMESTAMP", type datetime}, {"Start", type datetime}, {"End", type datetime}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Key", each Text.Combine({Text.From([ID], "de-DE"), Text.From([INBOXID], "de-DE")}, ""), type text),
    #"Added Custom2" = Table.AddColumn(#"Inserted Merged Column", "StartDate", each Date.From([Start])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndDate", each Date.From([End])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"StartDate", type date}, {"EndDate", type date}})
in
    #"Changed Type1",

Source = NameOfTheQueryThatHasPreviouslyBeenThere,
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Partition", each _, type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"ID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "CallFunction", each function([Partition])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Partition"}),
    #"Expanded CallFunction" = Table.ExpandTableColumn(#"Removed Columns1", "CallFunction", {"ID", "INBOXID", "DTTIMESTAMP", "Index", "Start", "End", "Key", "StartDate", "EndDate"}, {"ID", "INBOXID", "DTTIMESTAMP", "Index", "Start", "End", "Key", "StartDate", "EndDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded CallFunction",{{"Start", type datetime}, {"End", type datetime}, {"StartDate", type date}, {"EndDate", type date}, {"DTTIMESTAMP", type datetime}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type",{"DTTIMESTAMP"})
in
#"Removed Columns2"

[amuola · Accepted solution]

Hi,

this was exactly what I needed, thank you so much!

Regards Amund