Minutes worked per hour
Hello folks, I am hoping someone can help me as I've searched forums and wrangled with CoPilot to no avail so far! I have timesheet data nicely formatted with a START_TIME and END_TIME and needed to show this in a table with NAME and CALENDAR_DAY and then columns from 00 to 23 to shade in when they have worked accross those hours. I have done this part using the DAX: 06 =IF(AND(HOUR(Timesheets[START_TIME Floor])<=06,HOUR(Timesheets[END_TIME Ceiling])>06),1,BLANK()) (obviously, 06 adjusted to each hour, then conditional formatting to shade a colour where '1') However, for the Start and End I need to shade a different colour if they don't start/end right on the hour. I thought the best way is to amend the DAX above to just show the number of minutes worked per hour and apply conditoinal formatting to that instead. But I can't get this to work. I used this DAX: 07 = SUMX( FILTER( Timesheets, Timesheets[START_TIME Revised] < TIME(8, 0, 0) && Timesheets[END_TIME Revised] > TIME(7, 0, 0) ), VAR StartTime = MAX(Timesheets[START_TIME Revised], TIME(7, 0, 0)) VAR EndTime = MIN(Timesheets[END_TIME Revised], TIME(8, 0, 0)) VAR MinutesWorked = IF( StartTime < EndTime, DATEDIFF(StartTime, EndTime, MINUTE), 0 ) RETURN MinutesWorked ) However, this gives a wild number as you can see: Essentially, the screenshot above just returns a 1 or 0 if someone worked in that hour, but i'd like to get the number of minutes worked in that hour. For example, if someone worked 09:30 to 13:00 it would show 30,60,60,60 accross the four columns. Thank you for any help in advance 🙂
Power BI DAX: Time Duration calculations displayed as [h]:mm:ss over 24 hrs (145:33:24)
I need assistance with calculating the sum of all the time taken for tasks being done. None of the existing solutions I have tried work as I need them to. The RADACAD example works for the card visual. I have a two tables that have the same columns but are for two different departments, I could append the tables as a single table, but I don't think it makes a difference at this point. The source files are imported from xlsx and the column Duration is General and appears as 03:10:10 It is then imported with Power Query using the Folder option and making changes to the sample file. I need to be able to sum this duration column in Power BI, however I do need to calculate total per user, filtering different dates. I have used transorm to duration, then in DAX SUMX and SUM the values using the var method, splitting and concatinating these values to RETURN a value. This is all good and well if I need to display the total on a card but does not work when using it in a table layout for users not generating data. My Employee table has everyone in, I can filter by department, group, function, manager or any of the other filter values. My data: Employee Duration Emp1 00:00:00 Emp1 null Emp2 00:00:08 Emp2 null Emp2 00:00:00 Emp1 00:26:51 Emp3 00:00:17 Emp4 00:00:09 My requirement is as follows: I need to display the value in hours exceeding 24 hours. As an example 145:33:24 When using the table visual, I drop the group and employeename in the rows field. I also use a slicer on group. In the values field, I cannot use the DAX calculated duration as it then provides everyones time in the rows. I could filter, lock and hide the department, but the problem I have using this is the employees with no data/duration shows the time as :: which is not a usable value and just looks wrong. The values are not usable in a chart as they are in TEXT format. I have spent a lot of time looking for and trying various solutions and none work with the charts or tables thus far. Please help? (Are there future plans for PBI format to include [h]:nn:ss so that it works beyond 24 hrs or Duration to be included in the table Data Type after applying transformation? I don't know who to direct this to as it is probably the devs.)
Night shift (logic, calculation)
Hi everybody, I'm looking for a solution, but I can't find it. I would like to know the time worked. Info: - People work in the morning, afternoon and night. Overtime is also possible. - Thousands of posts are made in a day, that's thousands of time data. So there is not only a beginning and an end. - Morning shift: 06-14 (can be overtime: 02-14, 06-18) - Afternoon shift: 14-22 (can be overtime: 10-22, 14-02) - Night shift: 22-06 (can be overtime: 18-06, 22-10) MIN - MAX works in the morning and in the afternoon, because the time data comes one after the other. It does not work at night because it is separate, e.g.: 0:00 (start) -> (end) 6:00 + 22:00 (start) -> (end) 0:00. This is a MIN - MAX 24h. time worked = VAR balans = CALCULATE ( MAX (EVENTHistory[hour]), FILTER ( ALLSELECTED ( 'EVENTHistory' ), EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker] ) ) - CALCULATE ( MIN (EVENTHistory[hour]), FILTER ( ALLSELECTED ( 'EVENTHistory' ), EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker] ) ) RETURN FORMAT ( INT ( balans * 24 ), "00" ) & ":" & FORMAT ( INT ( MINUTE ( balans ) ), "00" ) & ":" & FORMAT ( INT ( SECOND ( balans ) ), "00" ) Thank you in advance for your help.
