topic Hour Breakdown in Quick Measures Gallery

Hour Breakdown

Greg_Deckler — Tue, 09 Apr 2024 17:32:34 GMT

This Quick Measure breaks a start and end time down into the number of minutes for each hour of the day. There are actually two measures included in order to demonstrate how to use the techniques in Measure Totals, The Final Word to display the correct totals and subtotals within a matrix.

Hour Breakdown = 
VAR __currentHour = HOUR(MAX('Hours'[Hour]))
VAR __startHour = HOUR(MIN('Data'[Start]))
VAR __endHour = HOUR(MAX('Data'[End]))
VAR __table = GENERATESERIES(__startHour,__endHour,1)
VAR __table1 = ADDCOLUMNS(__table,"__minutes",
    SWITCH(TRUE(),
        __startHour < __endHour && [Value] <> __endHour && [Value] <> __startHour,60,
        __startHour < __endHour && [Value] = __endHour,MINUTE(MAX('Data'[End])),
        60-MINUTE(MAX(Data[Start]))
    )
)
VAR __table2 = FILTER(__table1,[__minutes]>0)
RETURN
SUMX(FILTER(__table2,[Value] = __currentHour),[__minutes])

Hour Breakdown Total = 
VAR __table = SUMMARIZE('Data',[Date],[ID])
VAR __table1 = GENERATE(__table,Hours)
VAR __table2 = ADDCOLUMNS(__table1,"__duration",[Hour Breakdown])
RETURN
IF(HASONEVALUE(Hours[Hour]) && HASONEVALUE(Data[ID]),[Hour Breakdown],SUMX(__table2,[__duration]))

eyJrIjoiZjYxYjUzYTEtMTM5ZS00NjAwLWEyZjgtNmM4MmFjMzEyODBhIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9

Re: Hour Breakdown

Anonymous — Fri, 17 Dec 2021 15:11:34 GMT

Hello Greg_Deckler ,
I am learning powerBI. I followed the above solution but somehow it's not working for my data. I wanted to show employee count by the hour and date. Here is what the sample data look like:

Could you please help me? Any help on this is appreciated ‌‌

Thanks
Richa

Re: Hour Breakdown

Greg_Deckler — Mon, 20 Dec 2021 14:14:04 GMT

Anonymous Sure, if you could post that as text that would assist greatly.

Re: Hour Breakdown

dcrow5378 — Tue, 09 Apr 2024 17:15:47 GMT

Hi Greg_Deckler,
Wouldn't this be 36 instead of 24? If they started at 5:24 PM, they had 36 minutes in the 5 PM hour correct? Making the total 96 for 140 & 141 for the 5PM hour.
The 9:14 PM end time makes sense as they only had 14 minutes into the 9 PM hour.

Re: Hour Breakdown

Greg_Deckler — Tue, 09 Apr 2024 17:28:40 GMT

dcrow5378 Good catch. Easily fixed:

Hour Breakdown = VAR __currentHour = HOUR(MAX('Hours'[Hour])) VAR __startHour = HOUR(MIN('Data'[Start])) VAR __endHour = HOUR(MAX('Data'[End])) VAR __table = GENERATESERIES(__startHour,__endHour,1) VAR __table1 = ADDCOLUMNS(__table,"__minutes", SWITCH(TRUE(), __startHour < __endHour && [Value] <> __endHour && [Value] <> __startHour,60, __startHour < __endHour && [Value] = __endHour, MINUTE(MAX('Data'[End])), 60 - MINUTE(MAX(Data[Start])) ) ) VAR __table2 = FILTER(__table1,[__minutes]>0) RETURN SUMX(FILTER(__table2,[Value] = __currentHour),[__minutes])

Will update.

Re: Hour Breakdown

dcrow5378 — Tue, 09 Apr 2024 17:29:45 GMT

Greg_Deckler
Thanks! This is super helpful by the way!

Re: Hour Breakdown

dcrow5378 — Fri, 03 May 2024 16:02:09 GMT

Hey Greg,

Thanks Again for this. One more thing I was hoping you could help out with.
I am getting a full hour for an end time that is only 4 minutes into the end hour (or 0.07 of an hour)
Here are the punch times:

Here is the output I am receiving:

Any idea why I am getting a full hour for the 2:00 PM hour?

I am hoping to get the following for that row:

FYI, the formula for the Hour Total Column is just

Hour Total =

[Hour Breakdown Total]/60

Re: Hour Breakdown

dcrow5378 — Fri, 03 May 2024 19:22:33 GMT

I think I figured it out. Just need a third switch?

Hour Breakdown = VAR __currentHour = HOUR(MAX('Hours'[Hour])) VAR __startHour = HOUR(MIN(vTimecardTransaction[starttime])) VAR __endHour = HOUR(MAX(vTimecardTransaction[endtime])) VAR __table = GENERATESERIES(__startHour,__endHour,1) VAR __table1 = ADDCOLUMNS(__table,"__minutes", SWITCH(TRUE(), __startHour < __endHour && [Value] <> __endHour && [Value] <> __startHour,60, __startHour < __endHour && [Value] = __endHour,MINUTE(MAX(vTimecardTransaction[endtime])), __startHour = __endHour && [Value] = __endHour,MINUTE(MAX(vTimecardTransaction[endtime])), 60 - MINUTE(MAX(vTimecardTransaction[starttime])) ) ) VAR __table2 = FILTER(__table1,[__minutes]>0) RETURN SUMX(FILTER(__table2,[Value] = __currentHour),[__minutes])

Re: Hour Breakdown

dcrow5378 — Tue, 21 May 2024 22:18:04 GMT

Hey Greg,

One more question...hopefully.
I am not able to figure out what to do when the End Hour is less than the Start Hour. The entry below is not populating any time.

For Instance:

StartDateTime	EndDateTime
4/29/2024 10:00:00 PM	4/30/2024 12:00:00 AM

Re: Hour Breakdown

dcrow5378 — Wed, 22 May 2024 18:44:47 GMT

Solved and working great!

Hour Breakdown = VAR __currentHour = HOUR(MAX('Hours'[Hour])) VAR __startDatetime = MIN(vTimecardTransaction[starttime]) VAR __endDatetime = MAX(vTimecardTransaction[endtime]) VAR __startHour = HOUR(__startDatetime) VAR __endHour = HOUR(__endDatetime) -- Handle cases where end time crosses over midnight VAR __isCrossingMidnight = IF(__startDatetime > __endDatetime, 1, 0) VAR __adjustedEndHour = __endHour + 24 * __isCrossingMidnight -- Generate a series of hours, considering crossing over midnight VAR __table = GENERATESERIES(__startHour, __adjustedEndHour, 1) VAR __table1 = ADDCOLUMNS( __table, "__minutes", SWITCH( TRUE(), [Value] = __startHour && [Value] = __endHour + 24 * __isCrossingMidnight, DATEDIFF(__startDatetime, __endDatetime, MINUTE), [Value] = __startHour, 60 - MINUTE(__startDatetime), [Value] = __endHour + 24 * __isCrossingMidnight, MINUTE(__endDatetime), [Value] > __startHour && [Value] < __endHour + 24 * __isCrossingMidnight, 60, 0 ) ) VAR __table2 = FILTER(__table1, [__minutes] > 0) RETURN SUMX(FILTER(__table2, [Value] = __currentHour || [Value] = __currentHour + 24 * __isCrossingMidnight), [__minutes])