cancel
Showing results for
Did you mean:
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 name entry date Sarah 01.05.2020 Tom 15.3.2020

another table

 employee name Month salary Sarah 31.05.2020 3000 Sarah 30.6.2020 3000 Tom 31.3.2020 1400 Tom 30.4.2020 2800 Tom 31.5.2020 2800 ... ...

same with vacation

 employee name Month vacation days Sarah 31.05.2020 =25/12 Sarah 30.6.2020 =25/12 Tom 31.3.2020 =30/12 Tom 30.4.2020 =30/12 Tom 31.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 2020 May 2020 Sarah - 188 € Tom 160 € (2800/(20-(30/12)) 181 €
4 REPLIES 4
Community Support

HI @SarahAlsterspre,

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

Xiaoxin Sheng

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

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?

Community Support

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

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!

Announcements

#### Happy New Year from Power BI

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