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.