Calculate sick headcount

[fraitaan]

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!

[v-tangjie-msft · Accepted solution]

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. 

[amitchandak]

@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

[fraitaan]

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?