An old HR report in Power BI changes when the model only fetches today's state from the HR system. Every reorganisation, job change or retroactive correction then also rewrites the past in your report. The solution is a history of your own next to the HR system, with a snapshot every month, so you can see per reference date who worked in which job and in which department.
Below you can read where it goes wrong, in which situations the past shifts and how to build a history per HR system that stays put.
What happens when you load today's state
Most HR systems keep history. Asked an ordinary question, they still return the current state. AFAS templates, SAP SuccessFactors and a direct Workday report do that by default.
If you load that state into Power BI, the report calculates the past with today's data. An employee who moved from Purchasing to Logistics in June then also counts towards Logistics in January. Headcount for March last year no longer matches what you reported at the time. Last month's report and today's contradict each other, and nobody can explain why.
With Workday there is something extra. A direct report runs again on every refresh. The HR system is then queried over and over, and the outcome depends on the moment someone presses refresh.
Three situations in which the past shifts
A reorganisation. Departments are merged, split or renamed. Without a snapshot of the old structure you can no longer show the establishment from before the reorganisation. Questions like "how many people worked in this department last year" then no longer have an answer.
A retroactive change. In April, HR records that someone has had a different job since 1 February. Workday has an effective date and an entry moment for each data item. If you only look at the effective date, you do not see that February and March were changed afterwards, and you do not recalculate those months.
A correction. Besides a change, Oracle HCM also has a correction. A correction overwrites the past without a new row. So no trace is left in the data itself. A report from last month and a report from today then give a different figure for the same month.
Building history per HR system
A history that stays put has two parts: the history the HR system itself supplies, and a monthly snapshot that you record outside the HR system. How you fetch the first part differs per system.
- SAP SuccessFactors returns only today's time slice when asked an ordinary question. You have to request all time slices from an early start date.
- Workday: fetch by entry moment, and then recalculate the months that were changed by a retroactive change.
- HiBob: the effective date of each row in the work history determines per month who was in which team.
- BuddeeHR records job, department and FTE per period, but by default returns only the current arrangement. If you do not fetch the earlier periods, you calculate last year's establishment with today's hours.
- Nmbrs gives job and department a start period and not an exact date. The job history there follows the pay periods.
The monthly snapshot captures what the system itself does not keep, such as the old department structure after a reorganisation. How far back you can look therefore depends on two things: how much history the HR system supplies, and since when the snapshots have been running.
Edge cases that quietly skew your figures
Even with a history, a few things easily go wrong. They only stand out when someone puts two reports side by side.
- Payroll results from Visma Raet YouForce. The Data API supplies them only for the current and the previous calendar year. If you do not store them yourself per period, part of your multi-year overview disappears after a year-end.
- Fetching changes from SuccessFactors. Fetching by last-modified date sometimes returns a different time slice than you expect. A full load every week and the recent changes every day prevents that.
- Sickness across a month boundary. A sickness period from 20 March to 10 April should be split over both months. Otherwise the absence rate per month is wrong.
- Making corrections visible. When a correction in Oracle HCM changes an old month, you want to be able to see that in Power BI. A flag on the affected month answers the question of why an old figure was adjusted.
History is also the basis for good turnover figures. If you want to count leavers and headcount across months, read on under turnover and headcount.
How KoppelHet builds this
KoppelHet fetches the data from the HR system once a day, builds the history from it and records a snapshot every month. Power BI gets a fixed model to refresh on. That way the HR system is not queried again on every refresh, and a retroactive change lands in the right month. Months changed by a correction are recalculated and flagged.
We build that per organisation, with the same building blocks for history and snapshots, and the fetch rules that belong to your HR system. Examples are Workday to Power BI, SAP SuccessFactors to Power BI and AFAS to Power BI.
Read more about the topic on history by reference date and on the page HR data to BI. If you want to know what is possible with your system, look at Workday, Oracle HCM or Power BI.