Using data from a specific time period

[Tomhayw]

Hi there, 

I'm currently running some analysis on how much costs are acrued on a project (people costs only).

This is calculated by hourly rate x number of hours worked on that project.

I have already done this calculation, but, as people's hourly rates change, it will update this calculation and so a project in the past may now show higher costs (as the hourly rate has increased).

I'm currently capturing historic hourly rates and have an output something like this:

I want to incorporate this historic data to ensure that my projects don't get updated costs in the future.

Do you have any ideas on how to go about this?

Thanks,

Tom

[tevisyauw]

hi @Tomhayw  did your original data have the hourly rate validity period table? start period and end period. I think you must having that first on your database so it gonna be easy to design the report

for me personally, I usually put the start date valid (when change the person wage first time as start validity period , and then put end period validity when I'm going to update the wage as the end of period. 

[Tomhayw]

Hey @tevisyauw 

I haven't integrated the historic rates into my cardinality or logic.

I have start date and end date of projects, and I know what people's rates in 1 month as per the screenshot in the og post.

Here's what my ERD looks like:

[tevisyauw]

If your time entries table not record the wage at that time, i think your old data will be always updated following the updated wage. this happened to me few years ago, and my last resort to make manual history wage so I can manually provide history of the wage report that my client want to look at. 

I don't know about others, but for me You must start using historic rate table first. if not your PBI chart / table  will be always updated and changed.  

[Tomhayw]

Thanks, makes sense.

How would I incorpate using the correct rates at the correct time.

E.g. If someone's rate was £40 in 1/1/22 and a project started on 2/1/22, how would I link the two so the project knows to use the rate of £40?

[tevisyauw]

emm, it will be case by case base on your client company.
ex 1 : Andy rate was 40 in 01 Jan 2022 - 31 March 2022
and project last from 02 Jan 2022 - 25 March 2022

Then it will be only have 1 rate

ex 2 : Andy rate was 40 in 01 Jan 2022 - 31 March 2022 and the rate change to 42 in 01 Apr 2022 - 30 Jun 2022.

The project last from 02 Jan 2022 - 15 May 2022.

in my case i will average the rate between 02 Jan 2022 - 31 March 2022 (£40), and 01 Apr - 15 May 2022 (£42)

but IF the wage of the employee was project based then they will only use the first (£40) from 02 Jan 2022 - 15 May 2022