Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
@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...
It shows how to count employees over the time, but not about the salary?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |