Year on Year, Quarter on Quarter change in Salary

[Sandeep Warrier]

Hi All,

I have employee-wise salary data that I get every quarter. At a summary level, I need to compare the change in salary as of end of last fiscal year. For us, the fiscal year ends in March, so until next year June, all quarter data will be compared with current year March data.

Quarter End	EID	Salary
3/31/2022	1	2772
3/31/2022	2	2849
3/31/2022	3	2105
3/31/2022	4	2462
3/31/2022	5	2673
3/31/2022	6	2102
3/31/2022	7	2068
6/30/2022	1	2772
6/30/2022	2	2849
6/30/2022	3	2105
6/30/2022	4	2462
6/30/2022	7	2068
6/30/2022	8	2180
6/30/2022	9	2985
6/30/2022	10	2321
6/30/2022	11	2511
6/30/2022	12	2819

Here is a sample subset of the data. There are other employee identifiers like pay band, business unit etc., that I have not added here.

I created a calculated column called concat and concatenated EID and Quarter End Date. I then created a column called Baseline Salary, which will give me March salary and use the following formula -

Baseline Salary =
var OrignalBase = LOOKUPVALUE(Compensation[Salary],Compensation[Concat],Compensation[EID]&"@"&EOMONTH(EDATE(Compensation[Quarter End],IF(MONTH(Compensation[Quarter End])=12,-9,IF(MONTH(Compensation[Quarter End])=9,-6,IF(MONTH(Compensation[Quarter End])=6,-3,-12)))),0))
var FirstAvailSal = LOOKUPVALUE(Compensation[Salary],Compensation[Concat],Compensation[EID]&"@"&CALCULATE(MIN(Compensation[Quarter End]),ALLEXCEPT(Compensation,Compensation[EID])))
return IF(ISBLANK(OrignalBase),FirstAvailSal,OrignalBase)

And then I have a measure

YoY Incr = DIVIDE(AVERAGE(Compensation[Salary])-AVERAGE(Compensation[Baseline Salary]),AVERAGE(Compensation[Baseline Salary]))

However, when I pulled YoY Incr into a visual, I realised that it was not considering the true salary of March 2022, but was considering the salaries of all employees the are active in June 2022. For example, in the sample data above, the average salary for march would be 2433, but when I look at YoY in June, it considered only 2451.2 (EID 5 and 6 are not available in June data).

I then tried to create a new summary table using the SUMMARIZE function, but when I use the SUMMARIZE function in the visual, it started to give me very weird data and all dates between the quarters even though they don't exist in the table.

Using the table given above, I expect the following

Quarter End	YoY Increase
3/31/2022	0%
6/30/2022	3.05%

AVG(March) = 2433, AVG(June) = 2507.2, (June - March)/March = 3.05%

Would appreciate guidance on solving this.

Also posted at
Re: Year on Year, Quarter on Quarter change in Salary

Regards,
Sandeep