Forum Discussion
Helper IVWeekly Employee total by month
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
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)
3 Replies
- amitchandak
Super User
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-trend/ba-p/882970
Richard_HalsallThanks 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
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)