Forum Discussion
Calculate sick headcount
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.
Super Userfraitaan , 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?
