Forum Discussion

Tad's avatar
Tad
Frequent Visitor
4 years ago
Solved

Calculate Hours Employee should have worked

Hello all,   Your help is appreciated in advance. Sharing dummy data to help.   I am attempting to calculate the hours an employee should have worked given a start date, a termination date or tod...
Need Help
  • Tad's avatar
    Tad
    4 years ago

    I acheived my desired result by doing a DAX CROSSJOIN between the Calendar and the Combined Time Queries table.

    new table = CROSSJOIN(
    SUMMARIZECOLUMNS('Combined Time Entries'[Employee Name],'Combined Time Entries'[EMAIL_ADDRESS]),
    DISTINCT('Calendar'[Date]))
     
    NOTE: I have to use email address as it essentially the Employee ID between my databases.
     
    I then created calculated columns to check if the employee was employed on the date and another to check it the date was a weekday. I was then able to calculate how many hours the employee should have worked that day.
v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
4 years ago

Hi Tad 

Thanks for reaching out to us.

I've created a sample file based on your data, but could you please provide expected result of it? For example, a desired table picture and calculation formula(Although you provide the measure code, I can't tell if some logic is correct, so it would be better to provide the calculation formula directly), and then I will convert the formula to measure

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Tad's avatar
Tad
Frequent Visitor
4 years ago

I figured it out using a crossjoin. I needed a time entry for each employee for each day because the employees are not good about entering time and constantly miss a day here or there and I was having a hard time working up a formula that took that into account. So I fabricated date+emplooyee entries by crossjoining my dynamic calendar table with the employee names/ID (email addresses). I should remark: I appreciate the welcome. This is actually my second account on the forum as I switched jobs and lost access to my prior organization account. Previously I was one step shy of Super User. I don't know why this one threw me for a loop other than I have previously avoided doing DAX cross joins as I am more comfortable doing them in SQL.