Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 today if they have no termination date. I have 2-3 tables I have to check:
1 - the Calendar table with all of the dates and a teh weekday count column
2 - the time entry table with all time entries (billable, non-billable, pto, holiday)
3 - the employee status table that has start and term dates - I have done a simple lookup to have this added to each row for the given employee on table #2. so if the solution doesn't need this in a separate table then great.
Dummy Data:
#1 - Calendar:
Date | Weekday? |
5/1/2022 | 0 |
5/2/2022 | 1 |
5/3/2022 | 1 |
5/4/2022 | 1 |
5/5/2022 | 1 |
5/6/2022 | 1 |
5/7/2022 | 0 |
#2 Combined time Entries
Date | Name | Start Date | Hours Worked | Term Date | Last Day Worked |
5/2/2022 | John Doe | 4/1/2022 | 8 | 6/9/2022 | |
5/3/2022 | John Doe | 4/1/2022 | 8 | 6/9/2022 | |
5/1/2022 | Jane Doe | 4/1/2022 | 2 | 6/9/2022 | |
5/2/2022 | Jane Doe | 4/1/2022 | 8 | 6/9/2022 | |
5/3/2022 | Jane Doe | 4/1/2022 | 8 | 6/9/2022 | |
5/4/2022 | Jane Doe | 4/1/2022 | 8 | 6/9/2022 | |
5/5/2022 | Jane Doe | 4/1/2022 | 8 | 6/9/2022 | |
5/6/2022 | Jane Doe | 4/1/2022 | 8 | 6/9/2022 | |
5/7/2022 | Jane Doe | 4/1/2022 | 1 | 6/9/2022 | |
5/1/2022 | Joe Doe | 4/1/2022 | 8 | 5/3/2022 | 5/3/2022 |
5/2/2022 | Joe Doe | 4/1/2022 | 8 | 5/3/2022 | 5/3/2022 |
5/3/2022 | Joe Doe | 4/1/2022 | 8 | 5/3/2022 | 5/3/2022 |
#3 Employee Start and End Date:
Name | Start Date | Term Date |
John Doe | 4/1/2022 | |
Jane Doe | 4/1/2022 | |
Joe Doe | 4/1/2022 | 5/3/2022 |
Placeholder for max date | 1/1/1900 | 12/31/3000 |
Currently I am using the following formula to calculate what we call "Base Hours":
Solved! Go to Solution.
I acheived my desired result by doing a DAX CROSSJOIN between the Calendar and the Combined Time Queries table.
I acheived my desired result by doing a DAX CROSSJOIN between the Calendar and the Combined Time Queries table.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |