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