Forum Discussion
Counting Full Time Employees By Week
The best way to do this would be to make a Date table (https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/) with a Week column, with a 1:Many relationship with your original table (called Hours, for example). You can then write a measure like this to go in a visual with your week column.
Total Employees = VAR summarytable = SUMMARIZE ( Hours, Hours[Employee], 'Date'[Week], "@HoursWorked", SUM ( Hours[Hours] ) ) RETURN SUMX ( summarytable, IF ( [@HoursWorked] < 40, [@HoursWorked] / 40, 1 ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
The best way to do this would be to make a Date table (https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/) with a Week column, with a 1:Many relationship with your original table (called Hours, for example). You can then write a measure like this to go in a visual with your week column.
Total Employees =
VAR summarytable =
SUMMARIZE (
Hours,
Hours[Employee],
'Date'[Week],
"@HoursWorked", SUM ( Hours[Hours] )
)
RETURN
SUMX ( summarytable, IF ( [@HoursWorked] < 40, [@HoursWorked] / 40, 1 ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thanks! That worked great.