Average Overtime for the last 3 pay periods

[Anonymous]

Hi, requesting some assistance/insight on how to create a measure for the "overtime average of the last 3 pay periods." Based on the sample data below this would be the average of CYPP: 2112, 2113, and 2114. However, when data for next period is available, this would be for CYPP: 2113, 2114, and 2115 and so on every time a new pay period is added.

My end goal is to create a measure which subtracts the "overtime average of the last 3 pay periods" from the "overtime average across ALL pay periods". In the screenshot below, 5,064.81 - 5,552.17 = -487.37

Creating the measure for "overtime average across ALL pay periods" was fairly straight forward. This is what that measure looks like:

FY PP AVG =

AVERAGEX(

    KEEPFILTERS(VALUES('SAMPLE'[CYPP])),

    CALCULATE(SUM('SAMPLE'[AMT])))

I am able to accomplish the "overtime average of the last 3 pay periods" in a table by utilizing the Top N Filter (as pictured below), however, I cannot figure out how to create a measure which results in the same data, 5,064.81. 

This is the sample data used: (CYPP=Calendar Year Pay Period)

CYPP	NAME	AMT	DEPARTMENT
2020	SAL	2,048.69	A
2020	MIKE	860.96	A
2020	KIM	3,056.68	B
2021	SAL	1,428.96	A
2021	MIKE	1,932.96	A
2021	KIM	1,966.61	B
2021	JON	2,048.69	C
2022	SAL	860.96	A
2022	MIKE	3,056.68	A
2022	KIM	1,428.96	B
2023	SAL	1,932.96	A
2024	SAL	1,966.61	A
2024	MIKE	2,202.18	A
2025	SAL	2,235.83	A
2025	MIKE	2,269.49	A
2025	KIM	2,303.14	B
2025	JON	2,048.69	C
2025	BOB	860.96	C
2026	SAL	3,056.68	A
2026	MIKE	1,428.96	A
2101	SAL	1,932.96	A
2101	MIKE	1,966.61	A
2102	SAL	2,538.71	A
2102	MIKE	2,572.36	A
2102	KIM	2,606.02	B
2102	JON	2,639.67	C
2103	SAL	2,673.32	A
2103	MIKE	2,706.97	A
2103	KIM	2,740.63	B
2104	SAL	2,774.28	A
2104	MIKE	2,048.69	A
2105	SAL	860.96	A
2105	MIKE	3,056.68	A
2106	SAL	1,428.96	A
2106	MIKE	1,932.96	A
2107	SAL	1,966.61	A
2107	MIKE	3,009.85	A
2108	SAL	3,043.50	A
2108	MIKE	3,077.16	A
2108	KIM	3,110.81	B
2109	SAL	3,144.46	A
2109	MIKE	3,178.12	A
2110	SAL	2,048.69	A
2110	MIKE	860.96	A
2111	SAL	3,056.68	A
2111	MIKE	1,428.96	A
2112	SAL	1,932.96	A
2112	MIKE	1,966.61	A
2112	KIM	2,048.69	B
2113	SAL	860.96	A
2113	MIKE	3,056.68	A
2114	SAL	1,428.96	A
2114	MIKE	1,932.96	A
2114	KIM	1,966.61	B

Any suggestions, or solutions would be greatly appreicated!

Thank you, 

Marc

[Jihwan_Kim · Accepted solution]

Link to the sample pbix file 

[Anonymous]

This was perfect, thank you!