Forum Discussion

rdsknight11's avatar
rdsknight11
Regular Visitor
2 years ago
Solved

Offset with Reverse Cumulative Sum for Acct_ID

Hello, 


Apologies, I have checked other forumns on the offset function but they haven't helped my problem yet.

I have semi-successfully created a reverse cumulative sum that can work backwards from a known variable, ex. currrent_balance and subtract out each individual transaction amount to get the balance of each unique acct number in the past. The only problem is each calculated balance based on the reverse cumulative sum is displaying a date ahead of where I need it to be, so I need to offset my reverse_cumul sum calculation so that is always one transaction behind of where it is now.

 

I have created the below fake data to illustrate what I need help with. I believe the offset function can help but not able to get the formula working myself.

Acct Num (Fake)

Current Balance (As of Today)

Transaction Amount

Transaction Date

Reverse Cumul Sum

Balance as of Date

0004AB

$4,450

$42

6/6/2023 0:00

42

4492

0005AB

$9,999

($50)

12/14/2023 0:00

-50

9949

0005AB

$9,999

$21

5/1/2023 0:00

-29

9970

0007AB

$4,091

($9)

9/1/2023 0:00

-9

4082

0009AB

$9,023

($5)

10/16/2023 0:00

-5

9018

0009AM

$18

$6

9/9/2023 0:00

6

24

1010AB

$0

$32

12/14/2023 0:00

32

32

1010AB

$0

$89

12/11/2023 0:00

1122

1122

1010AB

$0

$1,001

12/11/2023 0:00

1122

1122

1010AB

$0

$43

12/9/2023 0:00

1165

1165

1111TE

$4,101

($14)

11/8/2023 0:00

-31

4070

1111TE

$4,101

($17)

11/9/2023 0:00

-17

4084

1111TE

$4,101

($30,325)

2/23/2022 0:00

15232

19333

1111TE

$4,101

$232

2/22/2022 0:00

15464

19565

1111TE

$4,101

$5,341

2/21/2022 0:00

20805

24906

1111TE

$4,101

($6,435)

3/2/2022 0:00

24044

28145

1111TE

$4,101

$434

3/1/2022 0:00

24478

28579

1111TE

$4,101

$8,000

2/20/2022 0:00

28805

32906

1111TE

$4,101

$30,242

11/1/2023 0:00

30211

34312

1111TE

$4,101

($534)

3/3/2022 0:00

30479

34580

1111TE

$4,101

$534

10/4/2023 0:00

30745

34846

1111TE

$4,101

$34

8/8/2023 0:00

30779

34880

1111TE

$4,101

$234

9/7/2022 0:00

31013

35114

1111TE

$4,101

$9,181

2/28/2022 0:00

33659

37760

1111TE

$4,101

$2,321

2/27/2022 0:00

35980

40081

1111TE

$4,101

($195)

2/24/2022 0:00

45557

49658

1111TE

$4,101

($102)

2/25/2022 0:00

45752

49853

1111TE

$4,101

$9,874

2/26/2022 0:00

45854

49955

1111TE

$4,101

($422)

2/17/2022 0:00

57406

61507

1111TE

$4,101

$28,942

2/19/2022 0:00

57747

61848

1111TE

$4,101

$81

2/18/2022 0:00

57828

61929

1111TE

$4,101

$1,424

2/16/2022 0:00

58830

62931

1111TE

$4,101

$9,132

2/15/2022 0:00

67962

72063

1111TE

$4,101

($8)

2/13/2022 0:00

68053

72154

1111TE

$4,101

$5

2/12/2022 0:00

68058

72159

1111TE

$4,101

$99

2/14/2022 0:00

68061

72162

1111TE

$4,101

$14

2/11/2022 0:00

68072

72173

1111TE

$4,101

$14

2/10/2022 0:00

68086

72187

1111TE

$4,101

($83)

2/8/2022 0:00

68936

73037

1111TE

$4,101

$933

2/9/2022 0:00

69019

73120

1111TE

$4,101

$431

1/5/2021 0:00

69367

73468

1654ET

$9,432

($483)

1/7/2023 0:00

-757

8675

1654ET

$9,432

($582)

1/9/2023 0:00

-582

8850

1654ET

$9,432

($444)

12/23/2022 0:00

-385

9047

1654ET

$9,432

$308

1/8/2023 0:00

-274

9158

1654ET

$9,432

$119

1/6/2023 0:00

59

9491

1654ET

$9,432

$697

1/6/2023 0:00

59

9491

1654ET

$9,432

($205)

12/19/2022 0:00

9516

18948

1654ET

$9,432

$10,000

12/22/2022 0:00

9615

19047

1654ET

$9,432

($633)

12/20/2022 0:00

9721

19153

1654ET

$9,432

$726

12/18/2022 0:00

10242

19674

1654ET

$9,432

$739

12/21/2022 0:00

10354

19786


with reverse cumul sum being a calculated column

Reverse Cumul Sum =

CALCULATE(SUM(Sheet1[Transaction Amount]),

FILTER(

    ALLEXCEPT(Sheet1,

        Sheet1[Acct Num (Fake)]), Sheet1[Transaction Date] >= EARLIER(Sheet1[Transaction Date],1)))

 

and balance as of date being...

Balance as of Date = Sheet1[Reverse Cumul Sum]+Sheet1[Current Balance (As of Today)]

Thank you for any help you can provide.