Help with Running Calculation - DAX

[ARomera]

Hi everyone,

I'm hoping someone can help me with a running calculation that I’ve been struggling to get working.

I’ve attached a link to the https://docs.google.com/spreadsheets/d/1yNDOkxsRPuLA_zxsdgapJm2jMqog9Fnf/edit?usp=drive_link&ouid=10... spreadsheet with the relevant data, and below is a snapshot of what I’m trying to achieve.

We work with a 13-period forecast, and in the data, I have a column for periods 1 to 13 and a column for EI $ which is calculated as EI % times the period salary+period bonus. However, there is a cap for the total EI amount for the year, which is shown in the EI Max Amount.

In the example provided, the EI $ would nearly reach the max by period 3, and in period 4, the amount would only be the remaining balance to meet the yearly cap. After the total EI cap is reached, all subsequent periods should display zero to ensure the forecast is accurate.

I really appreciate any help you can provide in getting this to work properly.

Thank you in advance!

Department	Type of Employment	Shared	Level	Bonus %	Status	Expense	Annual Salary	Adjusted Salary	Period	Toggle	Period Salary	Team Factor	Indivial Factor	Period Bonus	EI % Calculated	EI $ Calculate	EI MAX Amount
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	1	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	2	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	3	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	4	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	5	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	6	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	7	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	8	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	9	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	10	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	11	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	12	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24
Customer	Permanent	No	13	30%	Active	Customer	$211,150.00	$211,150.00	13	1	$16,242.31	75.00%	120.00%	$4,385.42	2.32%	$478.56	$1,466.24

[powerbiexpert22]

Hi @ARomera 

you will have to add index column in power query

[powerbiexpert22]

Hi @ARomera ,

Please try below , please see below pbix file for your reference

https://drive.google.com/file/d/1y1rUUZ7x6P-4dn7mFib_JsVI4WNNwyrZ/view?usp=drive_link

runningsum =

CALCULATE(sum(Sheet1[EI $ Calculate.2]),FILTER(all(Sheet1),Sheet1[Index]<=max(Sheet1[Index])))

runnsum1 =

IF([runningsum]<=max(Sheet1[EI MAX Amount.2]),[runningsum],0)

[ARomera]

Hi @powerbiexpert22 ,

Really appreciate your help on this. 

I tried the formula above, but for some reason the results shows zero to me to all the rows. Please, see below. Not sure if I am doing something incorrect, here are the screenshots:

Do I need to include anything else?

Thank you,

[v-rzhou-msft]

Hi @ARomera ,

I think your issue should caused by that you use ALL() in the measure. Here I suggest you to try ALLEXCEPT().

runningsum =
VAR _RunningSum =
    CALCULATE (
        SUM ( AOP[EI $ Calculate] ),
        FILTER (
            ALLEXCEPT ( AOP, AOP[Department], AOP[Type of Employment], AOP[Level] ),
            AOP[Period] <= MAX ( AOP[Period] )
        )
    )
RETURN
    IF ( _RunningSum <= MAX ( AOP[EI MAX Amount] ), _RunningSum, 0 )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ARomera]

@v-rzhou-msft , thanks for looking into it, I have used the formula above, but didnt get the results I needed. I have made few changes I and think I got some progress, however, I just cannot figure it out what I am doing wrong and why I cannot get the results calculated properly. 

Would you be able to help me figure it out what I am doing wrong?

I really appreciate your help.

Here is the formula I've included for the running calculation:

=

VAR _MaxAmount = MAX('AOP'[EI MAX Amount]) -- EI Max limit for the employee (1,466.24 annual)

VAR _RunningSum =

SUMX (

FILTER (

AOP,

AOP[Period] <= EARLIER(AOP[Period]) &&

AOP[INDEX] = EARLIER(AOP[INDEX])

),

AOP[EI $ Calculate]

)

VAR _RemainingAmount = _MaxAmount - _RunningSum -- Calculate the remaining amount before reaching the cap

RETURN

IF (

_RunningSum <= _MaxAmount,

IF (

AOP[EI $ Calculate] > _MaxAmount, -- condition to check if EI $ Calculate exceeds max amount

_MaxAmount, -- If it does, return EI Max Amount

MIN(AOP[EI $ Calculate], _RemainingAmount) -- Else, return the lesser of EI $ Calculate or the remaining cap amount

),

0 -- Return 0 if the cap is reached

Here is a screenshot of the table - I get the amount running, but the total doesnt match - it should be 1,466.24 as everyone will max out before YE.

I am also getting instances where it only calculates up to period 2 and then the total doesnt match as well.

Here is the snapshot of the data for the above one: