Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Help with Running Calculation - DAX

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_...
"DAX "
"DAX " "DAX help" 'Need help' 'DAX'
"Power Pivot"
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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,

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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: