Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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())

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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())

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.