Unmatching dates between payroll dates

[aflintdepm]

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

[ThxAlot]

Simple enough, just add a calculated column

[BeaBF · Accepted solution]

@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