Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Ben-Jy , Check this file, if this can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
@Ben-Jy , Check this file, if this can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
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
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
I solved my problem, I adapted the formula and everything is working fine now.
Thank you !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |