Forum Discussion

waffle85's avatar
waffle85
Regular Visitor
6 years ago
Solved

Counting Full Time Employees By Week

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 tim...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

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.