How to count the number of absent days dynamically?

[Gjakova]

Hi there, I was wondering how to count the number of absent days. Saw a lot of other examples on the community, but I'm not sure if I'm doing it the right way.

I have created a fake dataset which looks like my original model: https://www.dropbox.com/s/0ju70kfu3kxokrm/Sample%20file%20HR%20data.pbix?dl=0

So I have a table with Employees (all rows have an unique EmployeeID) and another table which registers the absent days etc. An employee could be absent due to illness, holiday etc.

What I would like is to have an overview of the amount of absent days per period. So when I slice per week I want to see how many absent days there were in a week, when I slice per month I want to see how many absent days there were in a month and so on...
I think the table 'SickLeave' is fine as it is, I just need to create a measure that count it and ONLY COUNTS when the REASON is "Sick", so Maternity leave etc. should NOT be counted in the measure.

My DAX skills are not that good, therefore I'm asking for some help.
If someone could also know how to count the current active employees, that would be an amazing extra!

Thanks in advance! If one needs more info etc. just shoot your question!

[v-lionel-msft]

Hi @Gjakova ,

Fill the [EndDate] = Blank() with TODAY()?

Best regards,
Lionel Chen

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

[Gjakova]

Good suggestion, thanks!

[FrankAT · Accepted solution]

Hi @Gjakova 

1. I rearranged your table SickLeave (see figure).
2. In the matrix visual I used the following maesure:

Count Days = CALCULATE(COUNTROWS(SickLeave),FILTER(SickLeave,SickLeave[Reason] = "Sick"))

Take a look at the pbix file attached below.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Gjakova]

Hi @FrankAT I tried your solution, but I don't understand how you handle the blank enddates? In your example they are left empty and thus not counted(?). Is there a way to fix that? Or should I leave it blank. Since the model is connected with an API, I think the null values automatically get adjusted once new data comes in?

But if they are blank, those people will not be counted, correct?

[guested]

Something like this should work for you:

SickDays = CALCULATE(SUMX(SickLeave, DATEDIFF(SickLeave[StartDate], SickLeave[EndDate],DAY)), SickLeave[Reason] = "Sick")

[Gjakova]

Hi @Anonymous thank you for your input, but I think it has to be more complex. An employee has a StartDate when he called in sick and an EndDate when he came to the office again (thus was better). So someone might have called in sick on October 5th and came back to the office at October 16th. So the amount of days he was sick would have been 10 working days.

In Power BI that event is registred as 1 row, so I don't think COUNTROWS is suitable for this.

I basically have to calculate all the days between StartDate and EndDate (EndDate could also be blank) for every row where the reason of absence was "Sick".

[Gjakova]

Hi @guested  Thank you for your response, but unfortunalety it is showing me the same value for every month and every year. I think it is a relationship problem? Do you have any more clue about it? Thanks again!

[Anonymous]

I can't get to your dataset at the moment but I believe you want a calculate and countrows like this
calculate(countrows('tablename'),SickdayColumnName="Sick")

calculate lets you add many filters and if it needs to be even more complex one of the calculate filters, can be a Filter() function as well.