Calculate working days lost per month

[craigdent]

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?

[lbendlin]

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 · Accepted solution]

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

[craigdent]

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().