Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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

 

Mathematical
Other
Time Intelligence
Totals

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

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

  • dcrow5378's avatar
    dcrow5378
    Resolver I
    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's avatar
      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.