Forum Discussion
Accumulate Surplus and Deficit
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!
3 Replies
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.
Anonymous
I have two files here
- the pbix file has two columns
- 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
- Refill_Gap_PDC: this is the column that identifies a surplus or deficit per dispense.
- 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.
- the pbix file has two columns
