Hour Breakdown

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 ...

HourBreakdown.pbix130 KB

Mathematical

Other

Time Intelligence

Totals

Greg_Deckler

Community Champion

2 years ago

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.

dcrow5378

Resolver I

2 years ago

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

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])

dcrow5378

Resolver I

2 years ago

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])