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

dcrow5378

Resolver II

2 years ago

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.

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.