Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Workdate | EmployeeID | Weekend | Hours Worked | Running Total | Flag at 40 |
4/4/2021 | A | 4/10/2021 | 10 | 10 | |
4/5/2021 | B | 4/10/2021 | 10 | 20 | |
4/6/2021 | A | 4/10/2021 | 10 | 30 | |
4/7/2021 | B | 4/10/2021 | 15 | 45 | x |
4/8/2021 | A | 4/10/2021 | 15 | 57 | x |
4/9/2021 | B | 4/10/2021 | 10 | 65 | x |
4/4/2021 | B | 4/10/2021 | 10 | 10 | |
4/5/2021 | A | 4/10/2021 | 10 | 20 | |
4/6/2021 | B | 4/10/2021 | 10 | 30 | |
4/7/2021 | A | 4/10/2021 | 12 | 42 | x |
4/8/2021 | B | 4/10/2021 | 10 | 55 | x |
4/9/2021 | A | 4/10/2021 | 10 | 67 | x |
4/10/2021 | B | 4/10/2021 | 10 | 75 | x |
4/11/2021 | A | 4/17/2021 | 10 | 10 | |
4/12/2021 | A | 4/17/2021 | 10 | 20 | |
4/13/2021 | B | 4/17/2021 | 10 | 10 | |
4/14/2021 | A | 4/17/2021 | 12 | 30 | |
4/15/2021 | B | 4/17/2021 | 10 | 20 | |
4/16/2021 | A | 4/17/2021 | 10 | 42 | x |
4/17/2021 | B | 4/17/2021 | 10 | 30 |
Solved! Go to Solution.
@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())
@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())
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |