Calculate Salary % in Advance Formulas Step in DataAction

[PS_1978]

Hello Team – I have a requirement to calculate "Salary %" as % of days an employee has spent in their 1st or last month of their employment.

For example, In working forecast, if an employee’s last day of the employment is 26th June 2025, I want their salary to be calculated as Total Salary * ( Last Working Day / No. of Days in the month ) è Total Salary *  ( 26 / 30 )  = Total Salary * ( 0.86 )

Logic in Advanced Formulas Step goes like below…

FOREACH.BOOKED [d/TIME] --> I have member set restricted for 18 months (forecast period)

// Derive # of days in Termination month

@DaysInMnth = DAYSINMONTH([d/HRPOSITION].[p/PLAN_TERM])

// Derive Day of the month

@NoOfDays = DAY([d/HRPOSITION].[p/PLAN_TERM])

// Calculate Salary Percentage as Day of Month over No.OfDays

@Salary = @NoOfDays / @DaysInMnth

ENDFOR

I used the variable, @Salary (defined as FLOAT) in the formula to assign it to the measure, SALARY and read it again to calculate the actual salary.

It works fine when I have only one employee with termination date (For ex: 2026-06-26) in the middle of a month.

But as soon as it finds more than one employee, it is taking average value instead of taking it from that employee’s termination date. When I tried to trace the record, it shows both the records in Combined Scope. Below screenshot is for two employees one with termination date as 2026-06-26 and the other with termination date as 2026-06-18

I have the termination date at model level as well. When I use, it considers the combined scope for the month and calculates number of days in month as 961, No of Days as 496 and salary as 0.51 (For example, TIME Dimension – 202510)

Any help here is greatly appreciated.

Thanks,

Phani

[N1kh1l · Accepted solution]

@PS_1978 

I do not think you need for each. Also using variables will only work in case of a single employee as variables are additive and when you run for more employees it will add values of 2 employees in those variables. i.e. variables do not get cleared when next employee records comes.

Also there is no data statement in your code so not sure how is this Advanced Formula posting values.

[N1kh1l · Accepted solution]

Looking at your data, if start date and end date are also dimensions, Just check using datediff between time and start date or end date to identify if employee is terminated or joined within same month. Then use DAYINMONTH and DAY to get the 2 values and get the ratio. Also check if you can use DATERATIO syntax as you have 2 dates in your data.

[PS_1978 · Accepted solution]

Hello @N1kh1l - You are correct. Variables are additive and I found it in a hard way. I modified the code and implemented the logic within data statement. It worked as expected. Why do you think I don’t need for each statement? For each employee, I would have 18 records – one each for each month in the forecast. I need to calculate head count as 1 and salary as either 1 or ratio based on number of days an employee is part of the organization. Without each, I would not be able to do this calculation. Isn’t it? Thanks, Phani

[William_Yu1]

I'm not sue how your data looks like, I assume you maybe don't the FOREACH loop. You may instead add a check if the month is the last month of the employee and calculate the ratio(even without the variable). 

[PS_1978]

Hello @William_Yu1 - Thanks for the response. Here is the sample data that gives an idea of how it looks like.

SOURCE DATA
TIME	START_DATE	END_DATE	EMPLOYEEID	DATASOURCE
202501	6/6/2022	6/18/2025	100100	UPLOAD
202502	6/6/2022	6/18/2025	100100	UPLOAD
202503	6/6/2022	6/18/2025	100100	UPLOAD
.	6/6/2022	6/18/2025	100100	UPLOAD
.	6/6/2022	6/18/2025	100100	UPLOAD
.	6/6/2022	6/18/2025	100100	UPLOAD
202606	6/6/2022	6/18/2025	100100	UPLOAD
202501	2/21/2025	6/26/2025	100101	UPLOAD
202502	2/21/2025	6/26/2025	100101	UPLOAD
202503	2/21/2025	6/26/2025	100101	UPLOAD
.	2/21/2025	6/26/2025	100101	UPLOAD
.	2/21/2025	6/26/2025	100101	UPLOAD
.	2/21/2025	6/26/2025	100101	UPLOAD
202606	2/21/2025	6/26/2025	100101	UPLOAD

I would like to transform this data as below

FINAL DATA
TIME	START_DATE	END_DATE	EMPLOYEEID	DATASOURCE	COUNT	SALARY
202501	6/6/2022	6/18/2025	100100	CALC-HC	1	1
202502	6/6/2022	6/18/2025	100100	CALC-HC	1	1
202503	6/6/2022	6/18/2025	100100	CALC-HC	1	1
202504	6/6/2022	6/18/2025	100100	CALC-HC	1	1
202505	6/6/2022	6/18/2025	100100	CALC-HC	1	1
202506	6/6/2022	6/18/2025	100100	CALC-HC	1	0.60
202502	2/21/2025	6/26/2025	100101	CALC-HC	1	0.29
202503	2/21/2025	6/26/2025	100101	CALC-HC	1	1
202504	2/21/2025	6/26/2025	100101	CALC-HC	1	1
202505	2/21/2025	6/26/2025	100101	CALC-HC	1	1
202506	2/21/2025	6/26/2025	100101	CALC-HC	1	0.87

Can you please advise what do you mean by without variable? Would you be able to give a sample code? I do have a check before calculating COUNT & SALARY measures. But they are not giving correct results.

Thanks,

Phani