Forum Discussion
Calculate sick headcount
Hi,
I've been searching around for a while and I've found a few formulas that somewhat works but doesn't go all the way.
I'm using this formula to calculate headcount at our company, and it works well:
Headcount = CALCULATE(
'Calculations'[Count of Employees]
, FILTER(
Merge1
, (
Merge1[Date of Hire] <= MAX(DimDate[Date])
&& Merge1[Date of Termination] > MAX(DimDate[Date])
) || ISBLANK(Merge1[Date of Termination])
))
Now I need to calculate how many people that were sick each day but i don't know how to "take away" the work-able employees.
Test sick people = CALCULATE(
'Calculations'[Count of Employees]
, FILTER(
Merge1
, (
Merge1[Sick from] <= MAX(DimDate[Date])
&& Merge1[Sick too] > MAX(DimDate[Date])
) || ISBLANK(Merge1[´Sick too])
))
This is the result:
The data could looks like this:
| Employee | Sick from | Sick too |
| ID-1 | 2022-11-10 | 2022-11-13 |
| ID-2 | 2022-11-01 | 2022-11-05 |
| ID-2 | 2022-11-10 | 2022-11-15 |
| ID-3 | null | null |
Hope I made it understandable.
If all employees are work-able/healthy, then the table should show nothing/blank and if someone is sick, it should be visible how many people, totally, are sick everyday. So in above example data it would be:
Sick people
2022-11-01: 1
2022-11-02: 1
2022-11-03: 1
2022-11-04: 1
2022-11-05: 1
2022-11-06: (blank)/0
2022-11-07: (blank)/0
2022-11-08: (blank)/0
2022-11-09: (blank)/0
2022-11-10: 2
2022-11-11: 2
2022-11-12: 2
2022-11-13: 2
2022-11-14: 1
2022-11-15: 1
2022-11-16: (blank)/0
Thanks!
Hi fraitaan ,
According to your description, you want to calculate the count of the sick people according to every date.
Here are the steps you can refer to :
(1)My test date is the same as yours.
(2)We can create a date table as a dimension table, and we do not need to create a relationship between two tables.
Date = CALENDAR( DATE(2022,11,1) ,DATE(2022,11,16))(3)Then we can create a measure:
Measure = var _cur_date = MAX('Date'[Date]) var _t = FILTER( 'Table' , 'Table'[Sick from]<=_cur_date && 'Table'[Sick too]>= _cur_date) return COUNTROWS(_t)+0(4)Then we can put the fields we need on the visual and we can meet your need , the result is as follows:
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
fraitaan , Try like
Test sick people = CALCULATE(
'Calculations'[Count of Employees]
, FILTER(
Merge1
, (
Merge1[Sick from] <= MAX(DimDate[Date])
&& ( Merge1[Sick too] > MAX(DimDate[Date])
|| ISBLANK(Merge1[´Sick too]) ) && not(isblank(Merge1[Sick from]))
)) )Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU
Hi amitchandak ,
Thanks for fast answer!
Now the healthy people have been removed but the count of sick people is wrong.
According to my raw data, 116 people were sick on first of november (tuesday) 2022. But now it only shows 95 people.
My count formula look like this:Count of employees = count(Merge1[ID])
Used count and not distinct count, because same person can be sick multiple times.
Also realised that when a person is sick 1 day, the from and too date is the same.Employee Sick from Sick too ID-2 2022-11-02 2022-11-02 Because the data is exported afterwards.
I saw that 21 people were sick only one day (2022-11-01 -> 2022-11-01) and that is the explanation to why we only see 95 in the above picture.
95 + 21 = 116
Got any other ideas?
Hi fraitaan ,
According to your description, you want to calculate the count of the sick people according to every date.
Here are the steps you can refer to :
(1)My test date is the same as yours.
(2)We can create a date table as a dimension table, and we do not need to create a relationship between two tables.
Date = CALENDAR( DATE(2022,11,1) ,DATE(2022,11,16))(3)Then we can create a measure:
Measure = var _cur_date = MAX('Date'[Date]) var _t = FILTER( 'Table' , 'Table'[Sick from]<=_cur_date && 'Table'[Sick too]>= _cur_date) return COUNTROWS(_t)+0(4)Then we can put the fields we need on the visual and we can meet your need , the result is as follows:
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
