Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

US Payroll per Period Comparision

Background: We process payroll two times a month: on the 15th of the month and the last day of the month. You may ignore issues around weekend/holidays for this problem.   Problem: I am trying to...
Need Help
Show and Tell
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    Try this measure:

    WagePreviousPeriod = 
VAR _Lat_Period =
    CALCULATE (
        MAX ( PayRegister[Payroll Date] ),
        FILTER (
            ALL ( PayRegister ),
            PayRegister[Payroll Date] < MAX ( PayRegister[Payroll Date] )
        )
    )
RETURN
    CALCULATE (
        [WageCurrentPeriod],
        FILTER ( ALLEXCEPT(  PayRegister,PayRegister[EMP ID]), PayRegister[Payroll Date] = _Lat_Period )
    )

     

    Output:

     

     

     

     

    Download the attached file.

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi Anonymous 

 

Try this measure to find last period Payroll:

WagePreviousPeriod = 
VAR _Lat_Period =
    CALCULATE (
        MAX ( PayRegister[Payroll Date] ),
        FILTER (
            ALL ( PayRegister ),
            PayRegister[Payroll Date] < MAX ( PayRegister[Payroll Date] )
        )
    )
RETURN
    CALCULATE (
        [WageCurrentPeriod],
        FILTER ( ALL ( PayRegister ), PayRegister[Payroll Date] <= _Lat_Period )
    )

 

Download the sample file that is attached.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi @VahidDM - thanks for the quick reply!

     

    Thanks, I think there are some unexpected results. It seems to be aggregating results and allowing evaluation at the date and the employee level.

     

     

    In this screenshot, value for 2/15/2022 should be $6,000 and for 2/28/2022 should be $4,900.

     

    In the above screenshot, the value for 2/15/2022 for employee 100 should be $1,000.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I modified the DAX code slightly to make the last piece of the code say equal to Last Period (as opposted to less than equal to Last Period). 

       FILTER ( ALL ( PayRegister ), PayRegister[Payroll Date] = _Lat_Period )

       

      However, it still evaluates at the period level but not at the employee level (I think it has something to do with the ALL function being used, it ignores the context for employee level. 

       

       

      Works okay.

       

       

      Does not evaluate at the employee level. 

       

      • VahidDM's avatar
        VahidDM
        Super User
        4 years ago

        Hi Anonymous 

         

        Try this measure:

        WagePreviousPeriod = 
VAR _Lat_Period =
    CALCULATE (
        MAX ( PayRegister[Payroll Date] ),
        FILTER (
            ALL ( PayRegister ),
            PayRegister[Payroll Date] < MAX ( PayRegister[Payroll Date] )
        )
    )
RETURN
    CALCULATE (
        [WageCurrentPeriod],
        FILTER ( ALLEXCEPT(  PayRegister,PayRegister[EMP ID]), PayRegister[Payroll Date] = _Lat_Period )
    )

         

        Output:

         

         

         

         

        Download the attached file.

         

        If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
        Appreciate your Kudos!!
        LinkedIn:         www.linkedin.com/in/vahid-dm/