Calculate not accumulated amounts from accumulated data structure

Question

Sorry for being so verbous, it's my very first post on this community.

I have source data structured as follows:

Periods or Index Field	Other Category	Accumulated Amounts
1	A	100
2	A	150
3	A	200
4	A	400
5	A	500
6	A	650
7	A	700
8	A	800
9	A	950
10	A	1100
11	A	1300
12	A	1350
13	A	1400
14 (*)	A	1500

(*) 14 is the maximum index for each category, but could be even less, 13 or 12.

I would like to have a calculated column with simple (not accumulated) amounts as well:

Periods or Index Field	Other Category	Accumulated Amounts	Not Accumulated Amounts
1	A	100	100
2	A	150	50
3	A	200	50
4	A	400	200
5	A	500	100
6	A	650	150
7	A	700	50
8	A	800	100
9	A	950	150
10	A	1100	150
11	A	1300	200
12	A	1350	50
13	A	1400	50
14	A	1500	100

I'm currently getting the result but pretty unefficiently, through pivoting the first column "Period or Index Field", in order to get 14 or whatever columns with accumulated data, and then I'm calculating each "Not Accumulated Amounts".

First Step: Pivoting Index column.

Other Category	1	2	3	4	5	6	7	8	9	10	11	12	13	14
A	100	150	200	400	500	650	700	800	950	1100	1300	1350	1400	1500
B	100	150	200	400	500	650	700	800	950	1100	1300	1350	1400	1500

Second Step: I have to change column field names, to identify accumulated amounts from not accumulated amounts:

Other Category	Acc-1	Acc-2	Acc-3	Acc-4	Acc-5	Acc-6	Acc-7	Acc-8	Acc-9	Acc-10	Acc-11	Acc-12	Acc-13	Acc-14
A	100	150	200	400	500	650	700	800	950	1100	1300	1350	1400	1500
B	100	150	200	400	500	650	700	800	950	1100	1300	1350	1400	1500

Third step, I manually calculate each not-accumulated amount, by subtraction:

NotAcc-1 = Acc-1

NotAcc-2 = (Acc-2) - (Acc-1)

NotAcc-3 = (Acc-3) - (Acc-2)

...

NotAcc-14 = (Acc-14) - (Acc-13).

Other Category	Acc-1	Acc-2	Acc-3	Acc-4	Acc-5	Acc-6	Acc-7	Acc-8	Acc-9	Acc-10	Acc-11	Acc-12	Acc-13	Acc-14	NotAcc-1	NotAcc-2	NotAcc-3	NotAcc-4	NotAcc-5	NotAcc-6	NotAcc-7	NotAcc-8	NotAcc-9	NotAcc-10	NotAcc-11	NotAcc-12	NotAcc-13	NotAcc-14
A	100	150	200	400	500	650	700	800	950	1100	1300	1350	1400	1500	100	50	50	200	100	150	50	100	150	150	200	50	50	100
B	100	150	200	400	500	650	700	800	950	1100	1300	1350	1400	1500	100	50	50	200	100	150	50	100	150	150	200	50	50	100

Last step: Unpivot both Acc-x columns and NotAcc-x columns, in order to transpose everything as desired, with further pivot-unpivot steps.

Hopefully someone can suggest a more efficient-elegant coding.

Example attached:

https://stevanatogroup.sharefile.eu/d-sb84ea3aaede640f2849d67982c4de728

Thank you.

M.

alexisolson · Answer

KT_Bsmart2gethe&nbsp;This can work but is highly inefficient when the tables get very large.
&nbsp;
marcofalzone01&nbsp;I'd recommend checking out these prior similar posts:
https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969
https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/2194983

kt_bsmart2gethe · Answer

Hi&nbsp;marcofalzone01&nbsp;,
&nbsp;
Based on the attached file, keep the first two steps (i.e. delete everything from the pivot step).
&nbsp;
Then, add a custom column and add code below:
[Accumulated Amount] - 
List.Sum(
    List.InsertRange(
        Table.SelectRows(
            #"Changed Type", 
            (x)=&gt; 
            x[Periods or Index Field]
             = [Periods or Index Field] - 1 
             and 
             x[Other Category]
             = [Other Category]
        )[Accumulated Amount]
    ,0,{0}
    )
)
&nbsp;
You should get the result below:

&nbsp;
The code contains two criteria: Other Category and Index.
&nbsp;
Regards
KT&nbsp;

kt_bsmart2gethe · Answer

AlexisOlson,&nbsp;I 100% agree with you.

Forum Discussion

Calculate not accumulated amounts from accumulated data structure

Periods or Index Field

Other Category

Accumulated Amounts

1

A

100

2

A

150

3

A

200

4

A

400

5

A

500

6

A

650

7

A

700

8

A

800

9

A

950

10

A

1100

11

A

1300

12

A

1350

13

A

1400

14 (*)

A

1500

Periods or Index Field

Other Category

Accumulated Amounts

Not Accumulated Amounts

1

A

100

100

2

A

150

50

3

A

200

50

4

A

400

200

5

A

500

100

6

A

650

150

7

A

700

50

8