cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sparkplug93751
Helper II
Helper II

Running total per employee per week where total resets at end of each week

Hello,

 

I want to calculate a running total column for employees during a single work week and flag each number day after they hit 40 hours (preferabbly 2 different measures)

 

Running total is per workday until we hit a new weekend date and flag at 40 is blank unless the running total is >40.

IE 4/7/21 for employeeid A when we hit 42 total hours worked, it is flagged. These below results are ideal. Thanks!

 

WorkdateEmployeeIDWeekendHours WorkedRunning TotalFlag at 40
4/4/2021A4/10/20211010 
4/5/2021B4/10/20211020 
4/6/2021A4/10/20211030 
4/7/2021B4/10/20211545x
4/8/2021A4/10/20211557x
4/9/2021B4/10/20211065x
4/4/2021B4/10/20211010 
4/5/2021A4/10/20211020 
4/6/2021B4/10/20211030 
4/7/2021A4/10/20211242x
4/8/2021B4/10/20211055x
4/9/2021A4/10/20211067x
4/10/2021B4/10/20211075x
4/11/2021A4/17/20211010 
4/12/2021A4/17/20211020 
4/13/2021B4/17/20211010 
4/14/2021A4/17/20211230 
4/15/2021B4/17/20211020 
4/16/2021A4/17/20211042x
4/17/2021B4/17/20211030 
1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@sparkplug93751 , a new column like

 


new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) && [EmployeeID] = earlier([EmployeeID])),[Hours] )
return
if(_sum >40, "X", blank())

 

or

 

 


new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) ),[Hours] )
return
if(_sum >40, "X", blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User IV
Super User IV

@sparkplug93751 , a new column like

 


new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) && [EmployeeID] = earlier([EmployeeID])),[Hours] )
return
if(_sum >40, "X", blank())

 

or

 

 


new column =
var _sum = sumx(filter(Table, [Weekend] = earlier([Weekend]) && [Workdate] <= earlier([Workdate]) ),[Hours] )
return
if(_sum >40, "X", blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors