Breakdown total salary into years

[kbi]

Hi all,

I have a staff total salary data with staff Start Date and End Date spanning multiple years.

What I would like to achieve is to break down this total salary into individual years in multiple rows, then calculate the daily rate and apply the salary for each year accordingly.

As I have large data with more than 3000 staff and complex start and end dates, I wonder if this can be done in Query Editor? If so could someone please suggest a solution? I attach here the excel file for your reference. Salary Yearly Breakdown.xlsx (11.7 KB)

Thank you and much appreciated.

[Nick_M]

I used a combination of Power Query and then a calculated column. When want to do look ups like a range of dates, PQ is just slow.

Steps:

1. Created a DimStaff table with a StaffKey. Merged that withe SalaryData table and your fact table. Wanted to get rid of the names themselves

2. Then in the Salary data table, you can get the daily rate by using the following calculated column
   [Total Salary] / Duration.Days([End Date] - [Start Date])
   

   

3. In the FactTable, can get the number of days by using the following calculated column
   Duration.Days([End Date] - [Start Date])
   

   

4. Load those tables and set up the data model as such:
   

   

5. In the Fact Table you can write the following calculated column which will go into the salary data table and pull out the correct average daily rate for the period in the fact table and multiple that rate by the “no of days” column in your fact table

=SUMX( 
	FILTER( 
		SalaryData, SalaryData[StaffKey] = FactTable[StaffKey]
		&& FactTable[Start Date] >= SalaryData[Start Date]
		&& FactTable[End Date] <= SalaryData[End Date]
	),
	CALCULATE (SUM( SalaryData[Daily Rate])) * FactTable[No Days]
)

Final Table:

Excel file:
Salary Yearly Breakdown Complete.xlsx (284.4 KB)

-Nick

[kbi]

Thanks for quick reply Nick.
The key thing in this exercise is the fact table. So you suggest to manually build the fact table with the year breakdown? Is there a way to do this automatically (Table 3 in your returned file)?

[Nick_M]

@kbi,
Sorry about that, this should be what you are after. Everything happens in the SalaryData table now (though I left the DimStaff table since would want to use that). You can click through the applied steps, but quickly:

1. Create a list of dates from the start and end date
2. Expand that out, then create a new column for start of year and end of year based on that column
3. Create a new column for Start Date to use. If the current start date > start of year, use the current start date, else use the Start of Year. Just the opposite for End date
4. Remove the other columns and only keep StaffKey, Start Use, End Date use, Total Salary and Daily Rate
5. Highlight StaffKey, Start Use, and End Date Use and remove duplicates
6. Subtract the EndDate Use and Start Use columns to get the No of Days
7. Multiply No of Days by Daily Rate, and this gives you the Total Yearly Salary
8. Remove columns you dont want and be sure to set data types

Final Table

Excel file:
Salary Yearly Breakdown Complete v2.xlsx (209.9 KB)

[kbi]

You are awesome @Nick_M!! This is exactly what I’ve been looking to resolve. Grateful for your help!