Forum Discussion
Calculate working days lost per month
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
Super UserI 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.
Super UserEasier 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!
- lbendlin
Yes, I neglected to mention that I cover that scenario in the Power Query portion. Should have done it in DAX via COALESCE().
