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

[SarahAlsterspre]

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 €

[v-shex-msft]

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

[SarahAlsterspre]

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?

[v-shex-msft]

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

[dedelman_clng]

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.