Forum Discussion
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 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
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/
5 Replies
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/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.
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.