I have a dataset, below is the one drive link of the sample datset for better understanding. It has a single date-time column. I need to write a measure that gives me the sum of difference of hours based on Code & labels. The column called "Code" is basically a parent column & has different levels which are located in "Level" column.
Need to write a measure that can give me the total time spent on each distinct code in "Code" column and also gives the bifurcation of time spent in hours on various corresponding levels in "Level" Column for the same code.
|Code||Time Spent||Code||level||Time Spent|
@hemantsingh thanks for the sharing the dataset but it is not very clear that how you want time spent to be calculated? Could you please explain with an example?
Time spent should get calculated in "Hours". All in all i need to show the the "total time spent" in Hours for each "Code". Further, if i bring in Level column than the time spent for each code should get bifurcated in the subsequent portions of time spent in each level that constitutes the Code.
I hope its clear. Leme know if you want futher more clarification or more detailed example.
Could you please describe more details about the logic you use to calculate Time spent? Take the highlighted codes for example, what difference do you refer to? Code 420 have 2 levels, do you want to calculate hour difference between the two levels(7/6/2017 4:17:00 PM and 7/8/2017 5:47:11 PM) in terms of Level scope for each code?
Also in terms of Code level, what hour difference do you want to return for code 420 and code 4545?
In terms of Code level, what hour difference do you want to return for code 420 and code 4545? Each code has serveral levels with different datetime, which specific datetime values for code 420 and code 4545 would you like to compare?