Forum Discussion
Calculate working days lost per month
Hi all, I'm hoping someone will be able to help me out.
I am trying to pull some absence reporting into Power BI and one of the measures I need to report on is the total number of working days lost each month for all employees who are off in each month, split by long term/short term and totalled.
I am looking to be able to produce a matrix that shows something like the following:
| Month | Sep-20 | Oct-20 | Nov-20 | Dec-20 | Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | Jun-21 | Jul-21 | Aug-21 |
| Days Lost Short Term | 80 | 75 | 95 | 21 | 50 | 66 | 94 | 84 | 74 | 69 | 84 | 68 |
| Days Lost Long Term | 120 | 142 | 135 | 211 | 184 | 156 | 174 | 146 | 169 | 183 | 122 | 135 |
| Total Days Lost | 200 | 217 | 230 | 232 | 234 | 222 | 268 | 230 | 243 | 252 | 206 | 203 |
An example of the data I am using can be found here: Sample Data which is split between the data itself and a date table - these are separate tables in PBI and currently have no relationship due to other measures in use.
Does anyone know of the best way to do this?
Easier said than done. This turned out to be a monster query. Please check that the results are as expected and that performance is acceptable.
see attached
4 Replies
- lbendlin
Super User
I think that by pre-bucketing ST and LT you are missing a data opportunity. Rather you would want to look at histograms, maybe even seasonal or monthly ones.
Speaking of months - I assume you want to count the number of days each person spends in either bucket each month?
For the computation you would want to approach it like this
- for each person and month you want to intersect the interval of start to end date of sickness with the start to end date of the month with the working day flag in the calendar. Then slap the bucket flag on and display the results.
- lbendlin
Easier said than done. This turned out to be a monster query. Please check that the results are as expected and that performance is acceptable.
see attached
This looks great, thank you for your time and effort on this!
I do get one error though, and it's because some of the end dates are not filled in because the person hasn't returned to work yet. To counter this I added a calculated column to add in today's date if it's blank or provide the end date which worked a treat! Thank you again!
