How to calculate total hours where consecutive weeks > 40 and hours are by week end date (sunday)

[dcormiernj]

We are implementing a bonus type plan and part of the calculation is to skip any PTO hours that are > 40 for any two or more consecutive weeks.  I have pasted below sample data and this data comes from a BI table. I have tried all kinds of BI things such as datesin period etc.  But the hours are by week end date.  I finally gave up and put the data into excel.  the excel formula that works and gives me what i want is pasted below.  A=username, G is total PTO hours

=IF(A2<>A1,IF(G2+G3>40,G2,0),IF(A2<>A3,IF(G2+G1>40,G2,0),IF(OR(G2+G1>40,G2+G3>40),G2,0)))

This formula gives me exactly what i want in the pasted table below column "PTO-AddBack"  but i cant for the life of me figure out how to do this formula in BI.

Can anyone help?

thank you

UserName	TimesheetEndDate	Total PTO Hours	Qualified	PTO-AddBack
John	4/9/2023		1	0
John	4/16/2023	8	1	0
John	4/23/2023	4	1	0
John	4/30/2023		1	0
John	5/7/2023	4	1	0
John	5/14/2023	8	1	0
John	5/21/2023		1	0
John	5/28/2023		1	0
John	6/4/2023		1	0
John	6/11/2023	8	1	0
John	6/18/2023		1	0
John	6/25/2023		1	0
John	7/2/2023		1	0
Mary	4/9/2023		1	0
Mary	4/16/2023		1	0
Mary	4/23/2023		1	0
Mary	4/30/2023		1	0
Mary	5/7/2023		1	0
Mary	5/14/2023		1	0
Mary	5/21/2023		1	0
Mary	5/28/2023		1	0
Mary	6/4/2023		1	0
Mary	6/11/2023		1	0
Mary	6/18/2023		1	0
Mary	6/25/2023		1	0
Mary	7/2/2023		1	0
Alex	4/9/2023		1	0
Alex	4/16/2023		1	0
Alex	4/23/2023		1	0
Alex	4/30/2023		1	0
Alex	5/7/2023		1	0
Alex	5/14/2023		1	0
Alex	5/21/2023		1	0
Alex	5/28/2023		1	0
Alex	6/4/2023		1	0
Alex	6/11/2023		1	0
Alex	6/18/2023		1	0
Alex	6/25/2023		1	0
Alex	7/2/2023	12	1	0
Chris	4/9/2023		1	0
Chris	4/16/2023		1	0
Chris	4/23/2023		1	0
Chris	4/30/2023		1	0
Chris	5/7/2023		1	0
Chris	5/14/2023		1	0
Chris	5/21/2023		1	0
Chris	5/28/2023	8	1	0
Chris	6/4/2023		1	0
Chris	6/11/2023		1	0
Chris	6/18/2023		1	0
Chris	6/25/2023		1	0
Chris	7/2/2023	8	1	0
Kathy	4/9/2023		1	0
Kathy	4/16/2023		1	0
Kathy	4/23/2023		1	0
Kathy	4/30/2023		1	0
Kathy	5/7/2023		1	0
Kathy	5/14/2023		1	0
Kathy	5/21/2023	20	1	0
Kathy	5/28/2023		1	0
Kathy	6/4/2023	4	1	0
Kathy	6/11/2023		1	0
Kathy	6/18/2023		1	0
Kathy	6/25/2023	40	1	40
Kathy	7/2/2023	19	1	19
Larry	4/9/2023		1	0
Larry	4/16/2023		1	0
Larry	4/23/2023		1	0
Larry	4/30/2023		1	0
Larry	5/7/2023		1	0
Larry	5/14/2023	8	1	0
Larry	5/21/2023		1	0
Larry	5/28/2023		1	0
Larry	6/4/2023		1	0
Larry	6/11/2023	40	1	40
Larry	6/18/2023	40	1	40
Larry	6/25/2023	40	1	40
Larry	7/2/2023	40	1	40

[dcormiernj · Accepted solution]

This is Awesome!  Thank you very much.

[v-jianboli-msft]

Hi @dcormiernj ,

Please try:

Column =
VAR _a =
    SUMX (
        FILTER (
            'Table',
            [UserName] = EARLIER ( 'Table'[UserName] )
                && [TimesheetEndDate]
                    = EARLIER ( 'Table'[TimesheetEndDate] ) - 7
        ),
        [Total PTO Hours]
    )
VAR _b =
    SUMX (
        FILTER (
            'Table',
            [UserName] = EARLIER ( 'Table'[UserName] )
                && [TimesheetEndDate]
                    = EARLIER ( 'Table'[TimesheetEndDate] ) + 7
        ),
        [Total PTO Hours]
    )
VAR _c =
    IF ( [Total PTO Hours] <> BLANK () && OR ( _a <> BLANK (), _b <> BLANK () ), 1 )
VAR _d =
    MAXX (
        FILTER (
            'Table',
            [UserName] = EARLIER ( 'Table'[UserName] )
                && [TimesheetEndDate] < EARLIER ( 'Table'[TimesheetEndDate] )
                && NOT (
                    [Total PTO Hours] <> BLANK ()
                        && OR ( _a <> BLANK (), _b <> BLANK () )
                )
        ),
        [TimesheetEndDate]
    )
VAR _e =
    MINX (
        FILTER (
            'Table',
            [UserName] = EARLIER ( 'Table'[UserName] )
                && [TimesheetEndDate] > EARLIER ( 'Table'[TimesheetEndDate] )
                && NOT (
                    [Total PTO Hours] <> BLANK ()
                        && OR ( _a <> BLANK (), _b <> BLANK () )
                )
        ),
        [TimesheetEndDate]
    )
VAR _f =
    IF (
        ISBLANK ( _d ),
        MINX (
            FILTER ( 'Table', [UserName] = EARLIER ( 'Table'[UserName] ) ),
            [TimesheetEndDate]
        ),
        _d + 7
    )
VAR _g =
    IF (
        ISBLANK ( _e ),
        MAXX (
            FILTER ( 'Table', [UserName] = EARLIER ( 'Table'[UserName] ) ),
            [TimesheetEndDate]
        ),
        _e - 7
    )
VAR _h =
    SUMX (
        FILTER (
            'Table',
            [UserName] = EARLIER ( 'Table'[UserName] )
                && [TimesheetEndDate] >= _f
                && [TimesheetEndDate] <= _g
        ),
        [Total PTO Hours]
    )
RETURN
    IF ( _c = 1 && _h > 40, [Total PTO Hours], 0 )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dcormiernj · Accepted solution]

This is Awesome!  Thank you very much.

[v-jianboli-msft · Accepted solution]

Hi @dcormiernj ,

Please consider about marking my reply if it helped you.
This will be able to better help people with similar problems. In addition, marking a reply that helped you also recognizes the person who helped you, which will better encourage users to respond positively.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.