topic Help with Running Calculation - DAX in DAX Commands and Tips

Help with Running Calculation - DAX

Anonymous — Wed, 02 Oct 2024 02:40:19 GMT

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=102505936425969572198&rtpof=true&sd=true 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

Re: Help with Running Calculation - DAX

powerbiexpert22 — Wed, 02 Oct 2024 06:40:57 GMT

Hi Anonymous ,

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)

Re: Help with Running Calculation - DAX

Anonymous — Wed, 02 Oct 2024 16:27:57 GMT

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,

Re: Help with Running Calculation - DAX

powerbiexpert22 — Wed, 02 Oct 2024 23:29:16 GMT

Hi Anonymous

you will have to add index column in power query

Re: Help with Running Calculation - DAX

Anonymous — Thu, 03 Oct 2024 07:33:58 GMT

Hi Anonymous ,

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.

Re: Help with Running Calculation - DAX

Anonymous — Thu, 03 Oct 2024 15:57:15 GMT

Anonymous , 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: