Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
craigdent
Frequent Visitor

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:

MonthSep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21
Days Lost Short Term807595215066948474698468
Days Lost Long Term120142135211184156174146169183122135
Total Days Lost200217230232234222268230243252206203

 

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?

1 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.

 

lbendlin_0-1632877678671.png

see attached

View solution in original post

4 REPLIES 4
lbendlin
Super User
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.

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.

 

lbendlin_0-1632877678671.png

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!

 

craigdent_0-1632903640011.png

 

Yes, I neglected to mention that I cover that scenario in the Power Query portion. Should have done it in DAX via COALESCE().

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.