Sick leave visualization (start and end dates)

[mrverns]

Hi everyone! I've been trying to solve the following by my own but I'm way too far of it.

The thing is: I need to analyze some HR data to know which and how many workers are currently with covid or sick leave.

In the attached file you can find a dataset example without calendar table. Note that there's a historical table, so workers (name and id) can be repeated.

Dictionary:

COLUMN	MEANING
name	worker name
id	worker id number
startDate	first day of sick leave
endDate	last day of sick leave
days_sickLeave	total sick leave's days

Expected results:
- A table in which rows shows a list of workers with current sick leave or, in other words, absent. Plus, the amount of days' remaining for each worker to 'come back' and its respective date.
- Show a card with the amount of workers with current sick leave.

What I think I have to do (but I couldn't make it):
- Identify the most recent or bigger [endDate] per [id] to know last sick leave period per worker.
- Compare each [endDate] with TODAY() to know current sick leave.
- I also tried to compare TODAY() with [startDate] and [endDate].

Attached file: https://www.dropbox.com/s/b344qpway3nb5ac/sickleave.pbix?dl=0

As you may note, I'm still with the basics. Thanks in advance!

[v-stephen-msft · Accepted solution]

Hi @mrverns ,

I didn't use the calendar table.

Firstly, I create a column to get the people on sick leave.

Is on sick leave = IF([startDate]<=TODAY()&&[endDate]>=TODAY(),"Y","N")

Then create another column to get the days' remaining who are on sick leave.

days' remaining = IF([Is on sick leave]="Y",DATEDIFF(TODAY(),[endDate],DAY),BLANK())

Current day is 7/14/2022, the result is below.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mrverns]

Thanks so much! I really appreciate it.

[amitchandak]

@mrverns , People on sick leave today

countrows(filter(Table, Table[Start Date] <= Today() && Table[End Date] >= Today()) )

Most Recent leave

Var _Max = maxx(filter(allselected(Table), Table[Start Date] < Today() ), Table[End date]) )

return

maxx(filter(allselected(Table), Table[end Date] =_max ), Table[ID]) )

Also, refer

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[mrverns]

This worked perfectly, even when I visualize it as a table, but with a column showing "1" in every row. Thanks @amitchandak 

countrows(filter(Table, Table[Start Date] <= Today() && Table[End Date] >= Today()) )

About the second code I'm having problems to completely understand it. Can u please explain me how does it work?