topic Re: Accumulating using previous in DAX Commands and Tips

Accumulating using previous

Casperserven1 — Wed, 14 Jun 2023 10:07:08 GMT

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,

Re: Accumulating using previous

tamerj1 — Wed, 14 Jun 2023 10:55:13 GMT

Hi Casperserven1
Is this a data table or a table visual? How did you get the first value (22)?

Re: Accumulating using previous

Casperserven1 — Wed, 14 Jun 2023 11:01:21 GMT

The first value (22) is the starting workforce.

Re: Accumulating using previous

Casperserven1 — Wed, 14 Jun 2023 11:01:51 GMT

and this is the raw data.

Re: Accumulating using previous

tamerj1 — Wed, 14 Jun 2023 12:18:39 GMT

Casperserven1
Please refer to attached sample file with the proposed solution

Total 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

Re: Accumulating using previous

Casperserven1 — Wed, 14 Jun 2023 13:37:09 GMT

Thanks the pbix worked perfectly, however when I tried the technique with other data this is what occurred...any ideas??

Re: Accumulating using previous

tamerj1 — Wed, 14 Jun 2023 13:45:26 GMT

Casperserven1
It should be a calculated column not a measure!

Re: Accumulating using previous

Casperserven1 — Wed, 14 Jun 2023 14:16:02 GMT

Thankyou so much , unfortunately it still isn't right. We only want the starting figure of 22 in the first row. after that is thould be adding and subtracting from the previous row.

Re: Accumulating using previous

tamerj1 — Wed, 14 Jun 2023 14:30:38 GMT

Casperserven1
Please clarify further. Perhaps with a screenshot?

Re: Accumulating using previous

Casperserven1 — Wed, 14 Jun 2023 14:52:53 GMT

yeah sure

Re: Accumulating using previous

tamerj1 — Wed, 14 Jun 2023 15:03:32 GMT

Casperserven1

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