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,

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

dcrow5378

Resolver I

2 years ago

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