Forum Discussion

aflintdepm's avatar
aflintdepm
Icon for Helper III rankHelper III
2 years ago
Solved

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...
Formula
How-to
  • BeaBF's avatar
    BeaBF
    2 years ago

    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

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

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