dcrow5378's avatar
dcrow5378
Icon for Resolver I rankResolver 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
    Icon for Community Champion rankCommunity 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's avatar
      dcrow5378
      Icon for Resolver I rankResolver I
      2 years ago

      Greg_Deckler 
      Thanks!  This is super helpful by the way!

    • dcrow5378's avatar
      dcrow5378
      Icon for Resolver I rankResolver 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's avatar
        dcrow5378
        Icon for Resolver I rankResolver 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])
    • dcrow5378's avatar
      dcrow5378
      Icon for Resolver I rankResolver 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:

      StartDateTimeEndDateTime
      4/29/2024 10:00:00 PM4/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's avatar
        dcrow5378
        Icon for Resolver I rankResolver 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])