Forum Discussion
Help with Running Calculation - DAX
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.
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: