Was able to spend some more time this week. For Total, there is an excellent Post Forum that you can refer.
For your solution, I have made few changes and modified measures as below.
- To calculated WorkHoursDue for Employee with AdmissionDate not blank. This will be dynamic based on Admission Date and Min/Max Date in Slicer.
WorkHours Due_BL = >
var AdmissionDate = max(‘Tab BusinessLeader’[Admission Date])
var MinDate = if(AdmissionDate < min(‘Tab Dates’[Date]),min(‘Tab Dates’[Date]),AdmissionDate)
var WorkHours = CALCULATE (
COUNT ( ‘Tab Dates’[Date] ) * 8,
FILTER (
‘Tab Dates’,
‘Tab Dates’[Date] >= MinDate
&& ‘Tab Dates’[Date] <= MAX ( ‘Tab Dates’[Date] )
),
‘Tab Dates’[Day Type] = “WeekDay” )
return
if(isblank(AdmissionDate),blank(),WorkHours)
- To Calculate for Individual Employees and Total.
WorkHours Due Final =
IF (
HASONEVALUE ( ‘Tab BusinessLeader’[Name] ),
[WorkHours Due_BL],
SUMX (
SUMMARIZE (
‘Tab BusinessLeader’,
‘Tab BusinessLeader’[Name],
“@Total”, [WorkHours Due_BL]
),
[@Total] ))
Similar Measures are created for Employee Actual WorkHours also. Employee Actual WorkHours measure contains all dates from Timesheet irrespective of Weekdays.
Attached the Final Solution. May need tweak if required. EDNA_Solution_Employee_Working_Hours.pbix (66.6 KB)
Thanks
Ankit J