Accumulate Surplus and Deficit

[hmayol]

Hello, 

I have a table with transactions, each transaction has a "gap". The gap indicates either a surplus (positive gap) or a deficit (negative gap). I am trying to calculate the accumulated deficit.

• The accumulated deficit depends on whether there is an accumulated surplus on the previous line to cover the current deficit or not. 
  • Current surplus (gap) cannot be applied to previous deficit (accumulated)
  • Current deficit (gap) can only be offset by previous surplus (accumulated)

Here is how I was able to achieve it in Excel: 

the formula for accumulated surplus =IF(C3+E2>0, 0, C3+E2)

the formula for accumulated deficit `=IF(C3<=0,D2,IF(AND(C3>0,C3+E2<0),D2,(C3+E2+D2)))

n	Gap	Accumulated Deficit	Accumulated Surplus
1	0	0	0
2	20	20	0
3	-12	20	-12
4	5	20	-7
5	11	24	0
6	0	24	0
7	-3	24	-3
8	1	24	-2
9	-7	24	-9
10	-2	24	-11
11	-1	24	-12
12	3	24	-9
13	0	24	-9

Any help would be appreciated! 

[Anonymous]

Hello! 

Did you figure out a way to do this? 🙂

[Anonymous]

@hmayol ,

Could you provide some sample data and the expected result, so that I can help you.

The information that you have provided is not clear. What is C3, E2 and D2. I think this are the cell references in the excel file.

Please provide sample data to understand your requirement.

Regards,

Pavan Vanguri.

[hmayol]

@Anonymous 

I have two files here 

1. the pbix file has two columns
   1. customer_id-patient_id-GPI10-dispense_index: this column serves as a dispense index. Each customer_id-patient_id-GPI10 values has n dispenses. The accumulated deficit needs to be calculated per customer_id-patient_id-GPI10 
   2. Refill_Gap_PDC: this is the column that identifies a surplus or deficit per dispense.
2. The xlsx file is a sample calculation of what the final outcome should be in power bi with one customer_id-patient_id-GPI10 example. 

Let me know if you need anymore information. 

Thanks, 

Hector M.