Help with Running Calculation - DAX

Question

Hi everyone,I'm hoping someone can help me with a running calculation that I’ve been struggling to get working.&nbsp;I’ve attached a link to the https://docs.google.com/spreadsheets/d/1yNDOkxsRPuLA_zxsdgapJm2jMqog9Fnf/edit?usp=drive_link&amp;ouid=102505936425969572198&amp;rtpof=true&amp;sd=true&nbsp;spreadsheet with the relevant data, and below is a snapshot of what I’m trying to achieve.&nbsp;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.&nbsp;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.&nbsp;I really appreciate any help you can provide in getting this to work properly.&nbsp;Thank you in advance!&nbsp;DepartmentType of EmploymentSharedLevelBonus %StatusExpenseAnnual SalaryAdjusted SalaryPeriodTogglePeriod SalaryTeam FactorIndivial FactorPeriod BonusEI % CalculatedEI $ CalculateEI MAX AmountCustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0011$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0021$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0031$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0041$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0051$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0061$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0071$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0081$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.0091$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.00101$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.00111$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.00121$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24CustomerPermanentNo1330%ActiveCustomer$211,150.00$211,150.00131$16,242.3175.00%120.00%$4,385.422.32%$478.56$1,466.24&nbsp;&nbsp;&nbsp;

powerbiexpert22 · Answer

Hi Anonymous&nbsp;,&nbsp;Please try below , please see below pbix file for your referencehttps://drive.google.com/file/d/1y1rUUZ7x6P-4dn7mFib_JsVI4WNNwyrZ/view?usp=drive_link&nbsp;runningsum =CALCULATE(sum(Sheet1[EI $ Calculate.2]),FILTER(all(Sheet1),Sheet1[Index]&lt;=max(Sheet1[Index])))&nbsp;runnsum1 =IF([runningsum]&lt;=max(Sheet1[EI MAX Amount.2]),[runningsum],0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;powerbiexpert22&nbsp;,&nbsp;Really appreciate your help on this.&nbsp;&nbsp;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:&nbsp;Do I need to include anything else?&nbsp;Thank you,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

powerbiexpert22 · Answer

Hi&nbsp;Anonymous&nbsp;you will have to add index column in power query

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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] &lt;= MAX ( AOP[Period] )
        )
    )
RETURN
    IF ( _RunningSum &lt;= MAX ( AOP[EI MAX Amount] ), _RunningSum, 0 )
Result is as below.

&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

anonymous · Answer

Anonymous&nbsp;, 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.&nbsp;&nbsp;Would you be able to help me figure it out what I am doing wrong?&nbsp;I really appreciate your help.&nbsp;&nbsp;&nbsp;Here is the formula I've included for the running calculation:&nbsp;=VAR _MaxAmount = MAX('AOP'[EI MAX Amount]) -- EI Max limit for the employee (1,466.24 annual)VAR _RunningSum = SUMX (FILTER (AOP,AOP[Period] &lt;= EARLIER(AOP[Period]) &amp;&amp;AOP[INDEX] = EARLIER(AOP[INDEX])),AOP[EI $ Calculate])VAR _RemainingAmount = _MaxAmount - _RunningSum -- Calculate the remaining amount before reaching the capRETURNIF (_RunningSum &lt;= _MaxAmount,IF (AOP[EI $ Calculate] &gt; _MaxAmount, -- condition to check if EI $ Calculate exceeds max amount_MaxAmount, -- If it does, return EI Max AmountMIN(AOP[EI $ Calculate], _RemainingAmount) -- Else, return the lesser of EI $ Calculate or the remaining cap amount),0 -- Return 0 if the cap is reached&nbsp;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.&nbsp;I am also getting instances where it only calculates up to period 2 and then the total doesnt match as well.&nbsp;Here is the snapshot of the data for the above one:&nbsp;&nbsp;&nbsp;

Forum Discussion

Help with Running Calculation - DAX

5 Replies

Related content

Calculate Run Rate

Running Maximum

Running sum

Running Monthly Total Calculation or Filter

Calculate a Running Daily Inventory

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