Hello,
I have a data set which includes daily time recording for every employee. There is start date which indicates employment start date and End Date = termination date or 12/31/99 if employee is still active.
What I’m looking for is to calculate number of workdays in the selected month, taking into account that some employees could start or end their employment during the month.
I have created calculated columns, which give me the accurate start and end dates:
Start Date Upd = IF( STARTOFMONTH( FactTable[WorkDate] ) >= FactTable[Start date], FactTable[WorkDate], FactTable[StartDate]
End Date Upd = IF( ENDOFMONTH( FactTable[EorkDate] <= FactTable[EndDate], ENDOFMONTH( FactTable[WorkDate] ), FactTable[EndDate] )
and now I’m stuck trying to determine how to calculate Workdays for each employee.
If I use a calculated column:
[Workdays w Holidays] = CALCULATE( COUNTROWS( Dates ), DATESBETWEEN( Dates[Date], FactTable[StartDateUpd], FactTable[EndDateUpd], Dates[IsWorkingDay] = TRUE, ALLSELECTED( FactTable ) ) it returns me accurate result but for each row. I can’t really use it, unless I use just count rows and then multiply by 8h to get monthly total hours to be worked.
When I try to create a measure instead it gives me an error message: A single value from column [Start Date Upd] cannot be determined.
I can’t think of a way to determine a start and end for each employee and calculate number of workdays in a month and total hours per month.
Later this measure [Total hours per month] will be used for comparison with actual hours worked.
Thank you
Hi @fudzen88, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.
A perfect initial question includes all of the following:
-
A clear explanation of the problem you are experiencing
-
A mockup of the results you want to achieve
-
Your current work-in-progress PBIX file
-
Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)
Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Not completing your data may sometimes cause delay in getting an answer.
The goal is to get effective and elegant way to get Monthly Expected Hours = Workdays (based on empl. start and end dates) * 8. And then calculate Monthly Variance = Monthly Expected hours - Monthly Total Hours
I created some measures which are working when I pull employee from Personnel table. But when I add additional attributes like Cost name or Supervisor name from other dim tables, it doesn’t work. All of those measures are based on [Workdays between start date and end] which I found here in the forum.
Thank you
timewriting.pbix (25.2 MB)
Hi @fudzen88,
Welcome on the forum !
General comments:
- Cost is not filtered correctly as the text Cost-key in the table Cost is smaller as the text Cost-key in the table Timewriting. (eg. 81113 vs 0000081113).
- It might be a good idea to integrate Lookup tables Person, Personnel Data and Cost into Api Person, unless changes needs to be stored and reported. (For example a change to another department)
I would like to present a solution for this capacity-reporting, solely based upon measures, which replaces the calculated columns and tables, and minimize data-redundancy and data-size.
PBIX included:
timewriting v16.pbix (298.7 KB)
Workout:
- The Timewriting-table contains only actual working hours. The capacity of working hours, taking into account start- and end dates of employee contracts, is calculated through measures, with no need for calculated columns and calculated tables.
- The data model is not changed.
- The start- and end date of an employee contract is moved to the lookup table “API-Person”, taken out of the fact-table.
- Note 1 : if capacity on employee changes needs to be monitored / tracked, using versions in the lookup table might be a solution (recommended).
- Four measures are set up (in the measure Group DS Measures):
-
Working Capacity in hours, excluding the weekends, whereby every person counts for one FTE (8 hours per day)
Total FTE Norm Hours =
Var WorkingDaysinMonth = FILTER( DateT,
NOT( WEEKDAY( DateT[Date])) IN { 1, 7} ) //Weekday default Return type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1 through 7.)
Var WorkingHoursperFTE = COUNTROWS(WorkingDaysinMonth) * 8 // based on FTE
Var TotalNormHours = // needed to calculate correct totals
SUMX(
ADDCOLUMNS(
VALUES( API_Person[No]),
"TotalTime", WorkingHoursperFTE),
WorkingHoursperFTE)
Return TotalNormHours
-
Note 2: to measure capacity of part-time employees, a column of % FTE could be added to the API-Person Lookup table.
-
Capacity adjustment, due to start- and end date of employee contracts
Contract Hours =
CALCULATE( [Total FTE Norm Hours],
FILTER( DateT,
COUNTROWS(
FILTER( API_Person,
DateT[Date] >= API_Person[DS StartDate] &&
DateT[Date] <= API_Person[DS EndDate] ) ) > 0 ) )
-
Note 3: in order to avoid calculation errors in this formula, the date table should be at least equal the end date of (permanent) employees, I have used an end date of 30/12/2030 (instead of 30/12/9999)
-
Calculation of total contract hours
Total Contract Hours =
SUMX( ADDCOLUMNS( VALUES( API_Person[No]),
"Total Contracted Norm Hours", [Contract Hours]), [Contract Hours])
-
Note 4 : for a correct calculation of totals, measure branching is necessary, integrating the three measures above into one does not provide the correct result
-
Finally a difference calculation between actual and capacity hours.
Difference Actual - Norm = [Actual Hours] - [Total Contract Hours]
Resulting in an example report as stated below:
If you have further questions or remarks, please let me know !
Kind regards, JW
2 Likes
Thank @deltaselect so much. It worked for me as a charm.
I have several questions though. Some of the look up tables like vw_person and API and Cost are pulling data from different sources. Do you suggest it’s a good practice to combine data in one lookup table vs creating relations between the tables? What is a better approach to combine data in one table: Power Query or through DAX in the tables?
I was trying to calculate number of employees with difference between Actual and Norm hours but it gives me the wrong answer. It should be pretty straightforward, but for some reason it’s not working.
Count Emp w Underwritten Hours =
COUNTROWS( ADDCOLUMNS( VALUES(API_Person),
“Diff”, [Difference Actual - Norm]>0) )
Actually, I played with the measure some more and made it work.
Count Emp w Underwritten Hours =
CALCULATE(COUNTROWS(FILTER( ADDCOLUMNS( VALUES(API_Person),
“Diff”, [Difference Actual - Norm]), [Diff] <0 ) ))
Please review and let me know if there is other ways of calculating this. Thank you
Hi @fudzen88 ,
Nice to hear that “it works as a charm”.
-
As the data model with the current Lookup table connections works, there is no necessity to integrate the lookup tables, but it seems cleaner to integrate.
-
With Power Query it is very easy to combine lookup tables at Home–> Merge Queries as New.
-
Power Query is in my opinion (much) better suited then DAX for this kind of integrations.
-
For easy integration, the different tables should have the same granularity/level of detail. (like one line per employee)
-
About the measure: there are alternatives, I would use:
Count Emp w Underwritten Hours = CALCULATE( COUNTROWS( VALUES( API_Person)),
FILTER( API_Person, [Difference Actual - Norm] <0 ))
-
Your measure (variant) is also possible:
Count2 Emp w Underwritten Hours = CALCULATE( COUNTROWS(
ADDCOLUMNS( SUMMARIZE( API_Person, API_Person[No]),"Emp.", [Difference Actual - Norm])),
FILTER( API_Person, [Difference Actual - Norm] <0 ))
See report below with the measures.
If you have further question, remarks, let me know.
Thank you for using the forum,
Kind regards,
JW
1 Like
Thank you very much JW @deltaselect . Truly appreciate your quick response.
