Salary Forecast

[AndrewI]

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

[sam.mckay]

Sorry for the delay on this.

I can only imagine no one has assisted on this probably because we need an example file to work through to really assist on this. Also it’s not really that clear what formula you are using in the image you’ve entered into the post here.

Model seems relatively simple so it shouldn’t be too difficult to work this out.

If you could add a demo Power BI model here of the scenario that would be helpful (not an PowerPivot one if possible)

Thanks
Sam

[AndrewI]

Hi Sam,
Thanks for the reply. Here is a link to a .pbix file

Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.

Thanks
Andrew

[Nick_M]

@AndrewI
Hope you do not mind, but I took a stab at this. There’s alot going on in that file and not 100% sure why you have two date tables, but using what was in the file, let’s see if this is what you had in mind.

Using your Remuneration measure it looked like it was correct at the row grand total for each employee name, but not at the column grand total of the month. Going on the belief that is true, I simply created a new measure to SUMX over the values of the Employee name using your original measure as the expression:
rem 2 = SUMX( VALUES( 'Monthly Salary'[Employee Name]), [Remuneration])

Without Months on the columns and just employees on rows you will get the following table:

and then when you add back in the columns:

Nick

[AndrewI]

Hi @Nick_M,

Thanks for the reply. I am not completely able to replicate your results. The Measure above does fix up my column Totals, however my Row Totals are still incorrect. did you change the model in some other way? I’ve updated the .pbix file above to include your measure.

Appreciate if you could take another look.

Andrew

[AndrewI]

Hi @Nick_M

You can disregard the last post. The issue was the bracket placement in the Remuneration Measure.

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

Thanks again for your help
Andrew

[Nick_M]

@AndrewI,
No problem at all, glad I was able to help

Nick