Get number of time a day has been taken as a day off

[Ben-Jy]

Hi,

In a PBI Dashboard, I must calculate the number of days off that have been taken by all the employees during a period of time.

To do that, I have a table with all the date from 2012 to 2042 that includes also a column that tell if the day is a working day and a column that indicates the number of employees who are on vacation on that date.

To do so, I only have a table with all the entries indicating the period of vacation taken by the employee :

With the two columns Start date (absencedata_von) and End date (absencedata_bis), I want to fill the first table that count the number of time a date has been taken as a day off.

The screenshot of the table above should fill the other table in this way :

I hope it is clear enough.

I already thank you for you help 🙂

Have a nice day !

Benjamin

[amitchandak · Accepted solution]

@Ben-Jy , Check this file, if this can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Ben-Jy]

It helps, even though I don't really understand the formula used.
However, I would need also to filter the day that are not working days, that is why I wanted to add a column that count the number of absences per day. This is what my "Date" table looks like : 

The column "Jour de travail" indicates if the day is a working day or not. If not, I don't want that those days are counted in the calculation.

PS : your table 'Sickness' in your pbix file has an 'id' column, is that necessary ? Because my table hasn't it.

Thank you

[v-deddai1-msft]

Hi @Ben-Jy ,

The Sickness[ID] column is same as emplyee name in your sample. Just based on amitchandak's sample, I suggest you create two measure:

workday = IF(WEEKDAY(MAX(DimDate[Date]),2)-6<0,1,0)

absence = COUNTROWS(FILTER(ALL(Sickness),Sickness[AbsenceStartDate]<=MAX(DimDate[Date])&&Sickness[AbsenceEndDate]>=MAX(DimDate[Date])))

Best Regards,

Dedmon Dai

[Ben-Jy]

I solved my problem, I adapted the formula and everything is working fine now. 
Thank you !