Salary Forecast


#1

Hi,

I am attempting to create a Monthly Remuneration report to forecast future salary outlay.

The requirements include the following:
Salary Increase due to Index and Promotions,
Prorata for staff starting/ending mid month.

I am pretty close to getting the desired result (See Below) but am being tripped up by the context of the Totals.

The DAX i’m using is as follows:

Compounding:=var Empl =IF(HASONEVALUE(‘Monthly Salary’[Staff Name]),VALUES(‘Monthly Salary’[Staff Name]))
RETURN
EXP(
SUMX(
FILTER(ALL(‘Monthly Salary’),‘Monthly Salary’[FYMonth]<=MAX(‘Monthly Salary’[FYMonth]) && ‘Monthly Salary’[Staff Name] = Empl),LN(1 + ‘Monthly Salary’[Increase])))

Projected Salary:=SUMX(‘Monthly Salary’,‘Monthly Salary’[Adjusted Monthly Salary]*[Compounding])

Num of Week Days:=VAR StartDate =MAX(‘Monthly Salary’[Effective Start Date])
VAR EndDate = MAX( ‘Monthly Salary’[Effective End Date])
RETURN
CALCULATE(SUM(‘Calendar’[WeekDay]),‘Calendar’[Date]>=StartDate,‘Calendar’[Date]<=EndDate)

Remuneration:=SUMX(‘Monthly Calendar’,[Total Week Days]/‘Monthly Calendar’[Week Days in Month])*[Projected Salary]

Appreciate if anyone has any ideas on how to fix up the DAX to show the correct amounts when the month filters are removed.

Thanks
Andrew