Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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
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.
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!
Yes, I neglected to mention that I cover that scenario in the Power Query portion. Should have done it in DAX via COALESCE().
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |