Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Rolling average working days lost using start and end date

I'm trying to work out the average working days lost for the business by day. The calculation that we use is to divide the total number of days absence over the previous year by the average full time eqivalent number of staff. We would need to see this figure for the last 12 months, meaning we'd need 2 years worth of data.


The absence report is set up to include a row for each absence, regardless of length. Each person will have a start and end date, and its this that we would use to determine whether a person has been off on a particular day, or not. 


The source of our data doesn't allow us to connect directly (i.e using a data warehouse), so we would have to export into Excel Power Query to manipulate it, before uploading to PowerBI. 


Any ideas?

Helper II
Helper II

@Greg_Deckler @v-kelly-msft apologies for the tardy response, but we've re-evaluated the requirments and found a solution to our problem. Happy to remove this thread if that's easier 

Community Support
Community Support

Hi @mbrierley ,


If you couldnt get help from what is suggested by @Greg_Deckler ,could you pls upload your .pbix file and expected output to onedrive business and share the link with me?Do remember to remove the confidential information.


Much appreciated.


Best Regards,
Did I answer your question? Mark my post as a solution!
Super User IV
Super User IV

Please see this post regarding How to Get Your Question Answered Quickly:

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors