Counting Full Time Employees By Week

[waffle85]

Hi all. I have a fair amount of experience with Tableau, but just switched to Power BI. I have a table that includes Employee, Hours, and Date Worked. I'm trying to calculate what percent of full time an employee is (40 hours worked in a week = 1.0; 20 hours = 0.5 ; 60 hours = 1.0) and then create a line chart that sums those values to show the total full time employee count by week.

Any help is greatly appreciated.

Thanks,

Mike 

[v-easonf-msft]

Hi ,  @waffle85 

If you have a date type column,just need to create a weeknum column as below:

Weeknum = WEEKNUM('Table'[Date Worked])

Then create calculate column as below:

1percent of full time by week = 
var a = CALCULATE(SUM('Table'[Hour]),ALLEXCEPT('Table','Table'[Weeknum],'Table'[Employee]))/40
return IF(a>1,1,a)

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat · Accepted solution]

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

[waffle85]

Thanks! That worked great.