Calculate employee working and sick data over 365 days

Occasional Contributor

Hi All,

I have two sets of employee data that I currently plan to merge manually - I am looking for a quicker way that is also accurate for the full year. The first set is employee data (by role or salary change) over 12 months - if an employee changes salary during the year, another row is added to reflect the change.

The second set is long-term sick data by employee - and the number of days between the dates needs to be deducted from the first set of employee data. The would be simple but if the date crosses over a salary change period, I need the rows to reflect the sickness at the two different salaries, or to sit in between as a negative for the other rows. FYI there might be 20k rows of employee data and 500 rows of sick data so doing this manually is not very appetising. Short example attached - many thanks in advance if anyone can come up with a solution!


2 Replies
best response confirmed by Hans Vogelaar (MVP)

@Brirack77  wow that was more complicated than I would've guessed or I just over complicated it.  I bet one of the people good with power query might have an easier solution but in the attached I have a couple options.

that's incredible - thank you *tips hat*