US Payroll per Period Comparision

[sidexcel]

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 create a measure to calculate payroll in the last pay period. Eg. for the month of October 31, 2021 the previous pay period will be October 15, 2021. 

I have a Fact Table (PayRegister) and a CalendarTable

Payroll Date	EMP ID	Salary
1/15/2022	100	1000
1/31/2022	100	1000
2/15/2022	100	1100
2/28/2022	100	1100
3/15/2022	100	1200

I have created the following measures:

PayDateCurrent = MAX(PayRegister[Payroll Date])   -- calculates current payroll date 

PayDatePrevious= 

IF (

DAY ( [PayDateCurrent] ) > 15,

EOMONTH ( [PayDateCurrent], -1 ) + 15,

EOMONTH ( [PayDateCurrent], -1 )  -- calculates previous payroll date

WageCurrentPeriod = SUM(PayRegister[Salary])

WagePreviousPeriod = CALCULATE( [WagesCurrentPeriod], [PayDatePrevious])

The problem I am facing is that WagePreviousPeriod is not showing a syntax error: Calculation error in measure 'PayRegister'[WagesPreviousPeriod]: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Does anyone have any recommendation on what edits I need to make? 

Here is a link to the file

[VahidDM]

Hi @sidexcel 

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/

[sidexcel]

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.

[sidexcel]

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 · Accepted solution]

Hi @sidexcel 

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/

[sidexcel]

Thank you - worked great!