Calculate monthly salary based on start date and end date

[Itzarthi]

Hey Guys,

I'm trying to calculate the montly salary fees based on start date/end date and salary. How can I realise this?

Hereby a preview of the data I have:

Employee Nmbr.	Startdate	Enddate	Salary
1	01-01-2018		€2000
2	01-06-2018	30-09-2018	€1800
2	01-07-2018		€2000
3	01-10-2018		€1750
4	01-01-2019		€1800

So if I select the following dates between 01-01-2018 and 31-07-2018 I want to see the following salary: €19.600 (€2000 x 7, €1800 x 2, €2000 x 1)

If it is possible I want to get the salaries per month per employee. Based on this information I think I can make a forecast.

I hope you guys can help me out!

[v-jayw-msft · Accepted solution]

Hi @Itzarthi ,

Check the formulas.

Measure = 
var startday = IF(MIN('date'[Date])<SELECTEDVALUE('Table'[Startdate]),SELECTEDVALUE('Table'[Startdate]),MIN('date'[Date]))
var endday = IF(ISBLANK(SELECTEDVALUE('Table'[Enddate])),MAX('date'[Date]),IF(MAX('date'[Date])>SELECTEDVALUE('Table'[Enddate]),SELECTEDVALUE('Table'[Enddate]),MAX('date'[Date])))
var diff = DATEDIFF(startday,endday,MONTH)+1
var months = IF(diff<0,0,diff)
return
SELECTEDVALUE('Table'[Salary])*months

Measure 2 = SUMX('Table',[Measure])

Result would be shown as below.

Best Regards,

Jay

[amitchandak]

@Itzarthi , My HR blog should help. You should display by month take max of salary and multiple by distinct count of month

refer current employee cal https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Itzarthi]

It shows how to count employees over the time, but not about the salary?