Excel Power Pivot Lookup

[RobF2112]

I have a table in an Excel file that contains weekly productivity performance for employees. The columns are:

1. Week_Ending (end date of week; weeks are Mon-Sun)
2. Supervisor_Name
3. Employee_ID
4. Kronos_Hours
5. Earned_Hours
6. Productivity_Pct (static value but calculated as Kronos_Hours/Earned_Hours)
7. Direct_Utilization_Pct

Data Table

I need to create a report from this table in which a user selects a range of week ending dates and employee IDs. The report should return:

1. Employee ID
2. Ending date of the first week the employee worked during the range
3. Ending date of the last week worked
4. Number of weeks from first week worked to last week worked
5. Productivity % for first week worked
6. Productivity % for last week worked
7. Rate of change of Productivity % from first week worked to last week worked

Employees may not have worked every week during the period. For example, if the range of week ending dates is 9/5-10/17, an employee may have only worked weeks ending 9/19 through 10/10.

I've used Power Query to add the table to the data Model and then Power Pivot to get 1, 2 (FIRSTDATE), 3 (LASTDATE), and 4 ((LASTDATE-FIRSTDATE)/7).

Power Query

Power Pivot

I need to do a lookup using the employee ID and first week (and again with last week) worked. I've been unsuccessful using DAX functions LOOKUPVALUE and CALCULATE (with and without FILTERS) to return 5 and 6. I can do it in Excel with XLOOKUP and SUMIFS, but using these with the returned pivot table gets ugly when rows are added or removed upon refresh.

I can calculate 7 in two ways.

• Excel's RRI function
• ([Last Productivity %]/[First Productivity %])^(1/[# of Weeks])-1

Report Output

I assume that if a Power Pivot solution can be found for 5 and 6, I could plug them into the second method so Power Pivot returns it, too.

Any suggestions on how to do this or a different approach I could use?

 

[alansidman]

All of your presentations are pictures. Cannot manipulate data in a picture and am not willing to try and recreate your data to solve your issues. Suggest you use XL2BB to upload a representative sample of your data. 8-15 records only. See my signature for instructions on XL2BB.

 

[RobF2112]

Thanks for the help wit XL2BB.

Data table:

Report.xlsx
	B	C	D	E	F	G	H	I
3	Week_Ending	Supervisor_Name	Employee_ID	Employee_Name	Kronos_Hours	Earned_Hours	Productivity_Pct	Direct_Utilization_Pct
4	10/17/2021		1		27.37	19.13	69.9%	68.4%
5	10/17/2021		2		5.58	31.01	555.4%	14.0%
6	10/17/2021		3		39.18	32.23	82.2%	91.7%
7	10/17/2021		4		35.75	46.65	130.5%	91.6%
8	10/17/2021		6		6.32	13.72	217.2%	19.7%
9	10/17/2021		7		43.53	38.67	88.8%	92.6%
10	10/17/2021		8		7.87	6.43	81.7%	17.0%
11	10/17/2021		9		7.68	12.51	162.9%	19.2%
12	10/17/2021		10		9.02	17.46	193.6%	24.1%
13	10/17/2021		11		8.28	13.23	159.7%	18.0%
14	10/17/2021		13		33.13	28.98	87.5%	82.3%
15	10/17/2021		14		36.45	25.62	70.3%	92.3%
16	10/17/2021		15		39.20	24.16	61.6%	95.2%
17	10/17/2021		16		21.20	9.59	45.3%	55.4%
18	10/17/2021		17		15.72	12.45	79.2%	50.3%
19	10/17/2021		18		24.28	18.41	75.8%	78.3%
20	10/17/2021		19		7.32	7.32	100.1%	91.7%
Weekly_Summary_Data

Desired output:

Report.xlsx
	E	F	G	H	I	J	K	L	M	N
1	Power Pivot					Excel formulas that I want to replicate in Power Pivot
2
3						XLOOKUP gets value from Weekly_Summary_Data tab			Alternative ways to calculate the same value
4
5
6					Criteria:	Employee_ID	Employee_ID		Excel's RRI Function	Arithmetic Operations
7						First Week	Last Week
8
9	Employee_ID	First Week	Last Week	# of Weeks		First Prod %	Last Prod %		Rate of Change	Rate of Change
10	1	09/05/2021	10/17/2021	6		63.5%	69.9%		1.60%	1.60%
11	10	09/05/2021	10/17/2021	6		78.6%	193.6%		16.21%	16.21%
12	101	09/05/2021	09/12/2021	1		80.2%	78.9%		-1.63%	-1.63%
13	102	09/05/2021	09/19/2021	2		16.8%	7.9%		-31.45%	-31.45%
14	104	09/05/2021	10/17/2021	6		76.3%	56.4%		-4.93%	-4.93%
15	105	09/05/2021	09/19/2021	2		66.2%	47.3%		-15.45%	-15.45%
16	106	09/05/2021	10/17/2021	6		87.2%	143.6%		8.67%	8.67%
17	107	10/10/2021	10/17/2021	1		0.0%	0.0%		0.00%	—
18	109	09/05/2021	09/12/2021	1		93.3%	121.3%		29.96%	29.96%
19	11	09/05/2021	10/17/2021	6		40.5%	159.7%		25.70%	25.70%
20	110	09/05/2021	10/17/2021	6		77.8%	70.4%		-1.64%	-1.64%
Report

Cell Formulas
Range	Formula
J10:K10	J10	=XLOOKUP($E10&F10,tbl_weekly_summary_data[Employee_ID]&tbl_weekly_summary_data[Week_Ending],tbl_weekly_summary_data[Productivity_Pct])
J11:K20	J11	=SUMIFS(tbl_weekly_summary_data[Productivity_Pct],tbl_weekly_summary_data[Employee_ID],$E11,tbl_weekly_summary_data[Week_Ending],F11)
M10:M20	M10	=IFERROR(RRI($H10,$J10,$K10),"—")
N10:N20	N10	=IFERROR(($K10/$J10)^(1/$H10)-1,"—")