0

I have a problem here, i would like to sum the work time from my employee based on the data (time2 - time 1) daily and here is my query:

Effective Minute Work Time = 24. * 60 * (LASTNONBLANK(time2,0) -FIRSTNONBLANK(time1,0))

It works daily, but if i drill up to weekly / monthly data it show the wrong sum as it shown below :

enter image description here

What i want is summary of minute between daily different times (time2-time1)

Thanks for your help :)

Edoard Putro Sukantio
  • 33
  • 4
  • Does your data model have one row per employee per day or multiple rows per employee per day? Are time1 and time2 both in the same table? – GregGalloway Nov 30 '15 at 12:02
  • Adding on to GregGalloway's - if you can provide a model diagram and description of constraints/guarantees about your data and/or sample data, that would help us very much in helping you. – greggyb Nov 30 '15 at 15:43

1 Answers1

0

You have several approaches you can take: the hard way or the easier way :). The harder (at least for me :)) is to use DAX to do this. You would:

1) create a date table,

2) Use the DAX calculate function to evaluate your last non-blank and first non-blank values (you might need to use calculate table, but I'm not sure; DAX experts jump in). Then subtract one vs. the other.

This will give you correct values for a given day for a given person. You can enforce the latter condition by putting a 'has one value' guard on the person name so that your measure informs the report author if they're not using it right.

Doing the same for dates is a little trickier. In the example you show you are including the date in the row grouping. But if you change your mind and want instead to have 'total hours worked by person' or 'total hours worked by everyone' you're not done with modelling yet.

Your next step is to use calculate table in combination with calculate to create a measure that returns the total. You'll use calculate table so you evaluate each date and the hours worked on that date by person. Then you'll use calculate to summarize that all down to a single number. If you're not careful with your DAX (or report authoring) you might mix which person you're summarizing for so that your first/last non blank are not at the person level. It gets intense quickly.

Your easier solution, though it might be more limited in its application - depends really on your scenario - is to use the query to transform the data into a summary by day and person using the group by command. This will give you a row per person per day with their start and end times. Then you can quickly calculate the hours worked on that day. Then you can quite easily build visuals on top of the summary data. Of course you give up some of the flexibility of the having a proper data model. However if you have a date table, a person table, and your summary table and then setup your relationships correctly you can achieve answers to the most common questions.

Lukasz P.
  • 2,219
  • 12
  • 21