Minutes worked per hour

[Wesley1979]

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 🙂

[Wesley1979 · Accepted solution]

Thank you for your reply. Unfortunately, the data used I am unable to get to upload.

On a birghter note, I managed to resolve using:
04 = IF(
HOUR(Timesheets[START_TIME Revised]) = 4 &&
MINUTE(Timesheets[START_TIME Revised]) <> 0,
60 - MINUTE(Timesheets[START_TIME Revised]) +
IF(
HOUR(Timesheets[END_TIME Revised]) = 4,
MINUTE(Timesheets[END_TIME Revised]),
0
),
IF(
HOUR(Timesheets[START_TIME Revised]) <= 4 &&
HOUR(Timesheets[END_TIME Revised]) > 4,
60,
IF(
HOUR(Timesheets[END_TIME Revised]) = 4,
MINUTE(Timesheets[END_TIME Revised]),
BLANK()
)
)
)

Thanks for any views. Worm Regards 🙂

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...