Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
@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...
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?
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
84 | |
70 |