topic Unmatching dates between payroll dates in DAX Commands and Tips

Unmatching dates between payroll dates

aflintdepm — Mon, 22 Jul 2024 15:05:16 GMT

Our company has 2 payroll cycles, A & B, that alternate weeks. Different pay groups are assigned to either A or B.

Occasionally, a correction payroll gets run that falls out of cycle with A or B dates. Is there a way to assign these out of cycle dates to the correct payroll date depending on cycle?

Example:

Payroll Group A pays on 6/7/24 and 6/21/24

A correction payroll for Group A is run on 6/12/24. Because this date is after 6/7/24, but before 6/21/24, I would want any values in this correction payroll assigned to the payroll cycle immediately prior.

Payroll Dates

Pay Date	Cycle
6/7/2024	A
6/14/2024	B
6/21/2024	A
6/28/2024	B

Sample Location Information

Location	Pay Date	Amount	Cycle
One	6/7/24	$100	A
Two	6/7/24	$250	A
Three	6/14/24	$200	B
Four	6/14/24	$250	B
One	6/12/24	$50	A

Based on the above example, I am looking for output like this

Payroll Date	Location	Total
One	6/7/24	$150
Two	6/7/24	$250
Three	6/14/24	$200
Four	6/14/24	$250

Re: Unmatching dates between payroll dates

BeaBF — Mon, 22 Jul 2024 15:55:29 GMT

aflintdepm Hi!

Create these two calculated columns in Location Table:

Previous Pay Date =

VAR CurrentDate = 'Location Table'[Pay Date]

VAR CurrentCycle = 'Location Table'[Cycle]

RETURN

CALCULATE(

MAX('Payroll Dates'[Pay Date]),

FILTER(

'Payroll Dates',

'Payroll Dates'[Pay Date] < CurrentDate &&

'Payroll Dates'[Cycle] = CurrentCycle

)

Effective Pay Date =

IF(

'Location Table'[Pay Date] IN VALUES('Payroll Dates'[Pay Date]),

'Location Table'[Pay Date],

'Location Table'[Previous Pay Date]

)

Then calculate the measure:

Total Amount =

CALCULATE(

SUM('Location Table'[Amount]),

ALLEXCEPT('Location Table', 'Location Table'[Effective Pay Date], 'Location Table'[Location])

)

and use this in your table, output:

BBF

Re: Unmatching dates between payroll dates

ThxAlot — Mon, 22 Jul 2024 17:36:14 GMT

Simple enough, just add a calculated column