cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

calculation costs per working day (- holidays and vacation) in a time span

Hey,

 

I have a problem calculating the costs per day for employees within a time span with the following formular:

Salary / (working days ( weekdays - general holidays) - sum(vacation per employee per month))

 

I have a table:

employee nameentry date 
Sarah01.05.2020 
Tom15.3.2020 

another table

employee nameMonthsalary
Sarah31.05.20203000
Sarah30.6.20203000
Tom31.3.20201400
Tom30.4.20202800
Tom31.5.20202800
...... 

same with vacation

employee nameMonthvacation days
Sarah31.05.2020=25/12
Sarah30.6.2020=25/12
Tom31.3.2020=30/12
Tom30.4.2020=30/12
Tom31.5.2020=30/12
...... 

I also have a Calendar which includes the working days - official holidays. with a column "workingdays/year" which have the nr 0 (no workingday) or 1 (workingday).

 

I had the solution: calculate(sum('Calendar'[workingdays/year]), DATESBETWEEN('Calendar'[Date], [MinDate], max('Calendar'[Date]))) - [vacation days]
MinDate = if(calendar(date) > entry date then calendar date else entry date)
 
This solution works, if only have a table in my report without time columns. Then it works fine. As soon as I add months columns it is not working, because it doesnt add the days of the employees but the vacation days. What I need, is:
 
 April 2020May 2020
Sarah-188 €
Tom160 € (2800/(20-(30/12))181 €
4 REPLIES 4
Community Support
Community Support

HI @SarahAlsterspre,

I'd like to suggest you take a look at the following link if they suitable for your requirement.

Count of Number of days for Employees based on START & END dates 
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hey, thanks for your reply. It doesnt work with this extra slicer. If I use an extra calendar as a basis for a slicer in my report, the items and diagrams dont react. Any suggestions?

Hi @SarahAlsterspre,

maybe you can try to create a calculated table to expand the date ranges and link to original tables, then you can use this table date field as the slicer to filter original tables records and calculate with measure formula.

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User II
Super User II

Can you provide more details on your model, your data, and any error messages you are receiving?  See this link for suggestions on how to give the community the information it needs to help: How to Get Your Question Answered Quickly 

 

Also, make sure you're using measures not calculated columns with you formula. Sometimes a simple mix-up like that can lead to not expected results.

 

 




Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors