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