Forum Discussion
Solved
Accumulating using previous
I am trying to do tthe following and struggling - I am sure this is easy but just can't see it.
| New Hires | Retirees | Total Workforce |
| 3 | 5 | 20 |
| 6 | 3 | 23 |
| 8 | 1 | 30 |
| 9 | 8 | 31 |
| 4 | 6 | 29 |
| 3 | 1 | 31 |
| 2 | 1 | 32 |
| 7 | 8 | 31 |
So in excel my first row = 22 +New Hires - Retirees
My second row = 20 (the result from the previous row) +NewHires - Retirees
My third row = 23 (the result from the previous row) +NewHires - Retirees
etc..
Tried various things including EARLIER, using indexing,
Now I got it. Please try
Total Workforce =
VAR StartingWF = 22
VAR TableBefore =
FILTER ( 'Table', 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
VAR Change =
SUMX ( TableBefore, 'Table'[New Hires] - 'Table'[Retirees] )
RETURN
StartingWF + Change
10 Replies
Hi Casperserven1
Is this a data table or a table visual? How did you get the first value (22)?The first value (22) is the starting workforce.
and this is the raw data.
Casperserven1
Please refer to attached sample file with the proposed solutionTotal Workforce = VAR StartingWF = 22 VAR TableOnAndBefore = FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ) VAR Change = SUMX ( TableOnAndBefore, 'Table'[New Hires] - 'Table'[Retirees] ) RETURN StartingWF + Change
