Rolling average working days lost using start and end date

[mbrierley]

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?

[mbrierley]

@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 

[v-kelly-msft]

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,
Kelly

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

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.