Forum Discussion
Unmatching dates between payroll dates
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 |
aflintdepm Hi!
Create these two calculated columns in Location Table:
Previous Pay Date =VAR CurrentDate = 'Location Table'[Pay Date]VAR CurrentCycle = 'Location Table'[Cycle]RETURNCALCULATE(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
2 Replies
Simple enough, just add a calculated column
aflintdepm Hi!
Create these two calculated columns in Location Table:
Previous Pay Date =VAR CurrentDate = 'Location Table'[Pay Date]VAR CurrentCycle = 'Location Table'[Cycle]RETURNCALCULATE(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
