topic Re: Accumulate Surplus and Deficit in DAX Commands and Tips

Accumulate Surplus and Deficit

hmayol — Fri, 10 Jan 2020 03:31:20 GMT

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!

Re: Accumulate Surplus and Deficit

Anonymous — Fri, 10 Jan 2020 09:04:49 GMT

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.

Re: Accumulate Surplus and Deficit

hmayol — Fri, 10 Jan 2020 12:00:50 GMT

Anonymous

I have two files here

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.
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.

Re: Accumulate Surplus and Deficit

Anonymous — Wed, 22 Apr 2020 08:43:30 GMT

Hello!

Did you figure out a way to do this? 🙂