cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Calculate monthly salary based on start date and end date

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.StartdateEnddateSalary
101-01-2018 €2000
201-06-201830-09-2018€1800
201-07-2018 €2000
301-10-2018 €1750
401-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!

 

1 ACCEPTED SOLUTION
Community Support
Community Support

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.

7.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
Community Support
Community Support

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.

7.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Super User IV
Super User IV

@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...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors