Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to do a fatigue calculation that does a running total of hours worked, and days in a row, but it resets when the person does not come in for work for one day. I have looked at the past posts, and can get the running total, but I do not know how to get the sum to restart when the person does not come in. This is what I want it do to:
Date | Employee Number | Time - Hours | Fatigue Hrs - running total | Days in row |
6/19/18 | 1002 | 7.42 | 7.42 | 1 |
6/20/18 | 1002 | 7.92 | 15.34 | 2 |
6/21/18 | 1002 | 1.18 | 16.52 | 3 |
6/23/18 | 1002 | 1.73 | 1.73 | 1 |
6/24/18 | 1002 | 0.97 | 2.7 | 2 |
6/25/18 | 1002 | 1.27 | 3.97 | 3 |
7/2/18 | 1002 | 5.08 | 5.08 | 1 |
7/10/18 | 1002 | 0.65 | 0.65 | 1 |
7/11/18 | 1002 | 3.5 | 4.15 | 2 |
7/13/18 | 1002 | 1.65 | 1.65 | 1 |
6/18/18 | 1004 | 9.77 | 9.77 | 1 |
6/19/18 | 1004 | 9.7 | 19.47 | 2 |
6/20/18 | 1004 | 11.67 | 31.14 | 3 |
6/21/18 | 1004 | 9.6 | 40.74 | 4 |
6/22/18 | 1004 | 8.12 | 48.86 | 5 |
6/23/18 | 1004 | 1 | 49.86 | 6 |
6/25/18 | 1004 | 9.7 | 9.7 | 1 |
6/26/18 | 1004 | 9.83 | 19.53 | 2 |
6/27/18 | 1004 | 9.65 | 29.18 | 3 |
6/28/18 | 1004 | 9.65 | 38.83 | 4 |
7/2/18 | 1004 | 9.83 | 9.83 | 1 |
7/3/18 | 1004 | 9.72 | 19.55 | 2 |
7/5/18 | 1004 | 9.63 | 9.63 | 1 |
7/6/18 | 1004 | 8.7 | 18.33 | 2 |
7/7/18 | 1004 | 1 | 19.33 | 3 |
7/10/18 | 1004 | 9.78 | 9.78 | 1 |
7/11/18 | 1004 | 9.77 | 19.55 | 2 |
7/12/18 | 1004 | 9.6 | 29.15 | 3 |
7/13/18 | 1004 | 8.48 | 37.63 | 4 |
7/14/18 | 1004 | 2 | 39.63 | 5 |
Thanks in advance for your help. This is my first post, but I have read a lot of different posts on MANY different questions. It has been very helpful. Great forum!
Solved! Go to Solution.
Please see the attached file with your sample data and above calculated columns
If you already have a days in a row column
you can use this calculated column to get the running total that resets
Running Total = VAR mydays = [Date] - [Days in row] + 1 RETURN SUMX ( FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] <= EARLIER ( [Date] ) && [Date] >= mydays ), [Time - Hours] )
Thanks, but I do not have days in a row either. I am tying to create the two last columns. I have been able to get a running sum for hours, but I need it to reset when the person does not come to work for one day. The count and the running total will then reset and start again once the person takes a day off.
Thanks for the help!
In that case, to get the Days in a Row column we can first add a supporting column which determines when running total is reset
FindCounterReset = VAR PreviousDate = MINX ( TOPN ( 1, FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] < EARLIER ( [Date] ) ), [Date], DESC ), [Date] ) RETURN IF ( [Date] <> PreviousDate + 1, "Counter reset" )
Now we can add the Days in Row Column as follows
Days in a Row = VAR counterstart = MINX ( TOPN ( 1, FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] <= EARLIER ( [Date] ) && [FindCounterReset] = "Counter reset" ), [Date], DESC ), [Date] ) VAR counterend_ = MINX ( TOPN ( 1, FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] > EARLIER ( [Date] ) && [FindCounterReset] = "Counter reset" ), [Date], ASC ), [Date] ) VAR counterend = IF ( counterend_ = BLANK (), DATE ( 3000, 1, 1 ), counterend_ ) RETURN RANKX ( FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] >= counterstart && [Date] < counterend ), [Date], , ASC, DENSE )
Now you can use the Column for running total in the previous post
Please see the attached file with your sample data and above calculated columns
That is AMAZING! It works!! Now I have to study it for a couple of hours to understand why.
THANKS A LOT!!
@Zubair_Muhammad I am trying to do something similar, but want the cumulative total per contract to reset at the end of the contract.
I have contract data like this:
CONTRACT INPUTS | |||||
BPID | Customer Name (BPID) | contract_num | SAP Contract Start | SAP Contract End | Contract Cap |
399 | ABC COMPANY (399) | 8003656409 | 2/1/2018 | 1/31/2019 | $ 8,000.00 |
399 | ABC COMPANY (399) | 8004081579 | 2/1/2019 | 1/31/2020 | $ 10,000.00 |
399 | ABC COMPANY (399) | 8004575589 | 2/1/2020 | 1/31/2021 | $ 9,000.00 |
And then I have transaction data. I want to plot the contract cap, which I figured out how to do, AND the cumulative data.
DATA INPUTS | DATA INPUTS | DESIRED RESULT | |
Order Submit Date | List_Price | Cumulative Usage | Notes |
2/1/2018 | 0 | Usage begins at 0 on 2/1/18, which is start of the contract above | |
2/21/2018 | $2,592 | $2,592 | |
8/23/2018 | $1,542 | $4,134 | |
10/12/2018 | $1,709 | $5,843 | |
10/17/2018 | $3,893 | $9,736 | |
12/4/2018 | $513 | $10,249 | |
2/1/2019 | $0 | Usage begins at 0 again on 2/1/19, with a new contract | |
2/25/2019 | $2,940 | $2,940 | |
5/14/2019 | $1,186 | $4,126 | |
7/18/2019 | $0 | $4,126 | |
8/23/2019 | $323 | $4,449 | |
9/2/2019 | $545 | $4,994 | |
9/24/2019 | $2,636 | $7,630 | |
2/1/2020 | $0 | Usage begins again at 0 on 2/1/20 | |
2/7/2020 | $1,737 | $1,737 | |
2/19/2020 | $128 | $1,865 | |
2/20/2020 | $128 | $1,993 |
The repair entitlements (blue line) plot correctly, but the cumulative value (red line) keeps accumulating instead of resetting to 0 at the start of a new contract.
Here are my formulas:
For this one I was trying to use the contract dates, but it does not accumulate, it only shows points in time for each repair (orange dots below).
Any help is appreciated. Thanks!
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |