Weekly Employee total by month

[Richard_Halsall]

Hi I have been trying to solve this issue for a while now

I have a dates table and also an employees table like this:

I have a measure that calculates total number of employees

I need to create a visual similar to this:

Which rather than showing total number of employees by month it will show the weekly employee number by month

For example Jan 2021 would equal 10.8 (54/5) Feb 2021 would equal 10 (40/4) Mar 2021 would equal 24 (96/4) etc

Any help would be appreciated. thanks

[amitchandak]

@Richard_Halsall , Create a date table have week number. Divide you current employee by distinct week numbers

column in date table

Week Number = WEEKNUM([Date],2)

measure =

[Current employee]/ distinctcount(Date[Week])

For current employee refer if this can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Richard_Halsall]

Thanks @amitchandak but I was expecting some different figures. If I look at March 2021 there are 4 full weeks and 3 days in the month, using the measure suggested gives 19.20, is there a way to include full weeks only -

so the result would be 24?

[amitchandak · Accepted solution]

@Richard_Halsall , Create these in you date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Full week flag =( [Week Start date] >= eomonth([date],-1)+1 && [Week Start date] <= eomonth([date], 0) && [Week End date] >= eomonth([date],-1)+1 && [Week End date] <= eomonth([date], 0) ,1,0)

Try measure like

[Current employee]/ calculate(distinctcount(Date[Week]), Date[Full week flag]=1)