Home Forums Power Pivot Labour costs that account for raises

This topic contains 2 replies, has 2 voices, and was last updated by  dionjl 4 days, 15 hours ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #12319

    dionjl
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    I am building a data model for my company based on data provided from staff timesheets. My model can currently take the timesheet data and relate it to the charge rates for hourly work or unit rate charges based on the activity. This gives me “revenue”. When the rates change, I have a lookup table where I add the new rates as a column (that gets unpivoted). This works fine because the rates only change at the beginning of every year.

    I want to build in “cost of labour” based on the employee’s wage/salary. Doing so would be a fairly simple lookup table; until I factor in raises! The issue I can’t wrap my head around is how to account for raises that happen in the middle of a year, or at some random date. In these cases the “cost of labour” for one hour of work yesterday might be different than the “cost of labour” for the same hour of work today. Plus not everyone gets raises on the same day (though they might), and some people might get multiple raises in one year. All of a sudden my simple lookup table seems more like a data table (or fact sheet) and I don’t know how I could relate that into my timesheet data table.

    Any help on this would be greatly appreciated. In general my Staff Info table will have columns similar to {Name, Effective Date, Pay Scheme (Salary or Hourly), Pay Rate, Vacation Allowance}. In this case all columns will have repeating values.

    #12322

    Andre
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    With rates changing over time it becomes a fact table indeed. If you design it properly you can use your date dimension table and employee table to connect both fact tables.

    #12358

    dionjl
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Ok, I think I can manage linking them, but I’m having issues using Power Query to create a suitable fact table. Right now there are distinct dates for when the change in wage occurs. What I believe I need is a list of dates for every day that that given wage rate is valid. I thought to use a custom column with the following formula (and then expand the column list):

    { Number.From([Date]).. Number.From(DateTime.Date ( DateTime.LocalNow()))}

    However, this produces a list of dates that extend to the current date for all wage rates. So Staff1 might be paid $25/hr as of January 1st 2018, and then receive a raise to $30/hr on November 1st 2018. But on November 1st, I would have two records (rows) wage rates for Staff1; one at the current pay rate and another at the previous pay rate.

    I either need to adjust that array formula for the custom column to find the next wage bump date and use that at the upper limit, or find another way to prepare the fact table.  Any help or pointers would be appreciated.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.