Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
This is Awesome! Thank you very much.
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.