Number of Work days from Hire date to now

[julsh41]

I need help!  I have a date table set up and columns to identify week days and holidays so I can calculate work days for the year, and it works perfectly for all employees that were here on Jan 1st, 2018.  What I can't seem to figure out is how to take the "Hired Date" from my employee list table and reduce available 2018 work days based on their hire date.  

Above is a list of new hires with their Hired on dates.  First, Everyone shows FULL available hours in the month they started, regardless if they started in the beginning of the month or the end.  Second, the total for everyone is the same 1592, which is the available hours total from Jan 1 to current.

Looking for some guidance.  Thanks

James

[v-danhe-msft]

Hi @julsh41,

Could you please post your data stracture and your desired result if possible?

Regards,

Daniel He

[mokhawaja]

Hi, i have the same requirement as @julsh41... and appreciate if @v-danhe-msft or @Arentir can help. here is my data structure:

1. Calendar table with a dates column
2. Employees Table (AppUsers) with the Names and include the hiring dates, with a relationship between the two tables from dates to HiringDate but inactive due to ambiguity with other relationships and tables in the dataset. created a Measure to calculate Workdays as follows:

WorkDays = CALCULATE(COUNT('Calendar'[Date]),'Calendar'[Weekday Num]<>5)*[Count of MedReps]

Note: [Count of MedReps] is a measure to count the Employees, and we have Friday only as a weekend!

now i have the following result screenshot of a table and date slicer:

so as you can see in the marks, the workdays (for the selected interval in the slicer) for two employees are incorrect, I tried to add a column with the following formula but the column does not respond with changing the slicer and shows wrong calculation: 

Workdays Adj = CALCULATE([WorkDays],FILTER('Calendar','Calendar'[Date]>=AppUsers[DateOfHire]))

so appreciate your help on this and should you need more info, i will be more than happy to provide.

regards,

Mohammad

[Arentir]

Hi @julsh41

It is hard to help you without looking at the model, any change you can share the model? (Feel free to remove any confidential information beforehand)

[julsh41]

That would be tough.  The model is pulling entirely from Quickbooks Online so I am not sure what will break if I delete employee and customer information.  I can try