Hi i have search for this answer already but cant find anything on it
In power BI, how would I make a table like this:
Employee | Sick Date | Hours |
123456 | 29/9/23 | 6 |
123456 | 2/10/23 | 8 |
123456 | 3/10/23 | 8 |
123456 | 15/8/23 | 8 |
456789 | 2/10/23 | 8 |
456789 | 2/8/23 | 4 |
456789 | 3/7/23 | 8 |
Look like this:
Employee | Start Date | End Date | Hours | Days Off |
123456 | 15/8/23 | 15/8/23 | 8 | 1 |
123456 | 29/9/23 | 3/10/23 | 22 | 5 |
456789 | 3/7/23 | 3/7/23 | 8 | 1 |
456789 | 2/8/23 | 2/8/23 | 4 | 1 |
456789 | 2/10/23 | 2/10/23 | 8 | 1 |
Now initially I thought this is just group by, min max date etc, however it has to take into account that if two dates are separated by a weekend, that even though the weekend row won’t show up in the table, it still needs to be included then combined into one occurrence. So if someone has one day off, that’s one occurrence, then next month for instance, they had a Friday and the following Monday off, it would say they have had another occurrence and that they had 4 days off work on that occurrence, even though technically they’ve only had two days off as they don’t work the weekend. Then months later, they have a Monday to Friday off, this would then be another occurrence, having 5 days off.
Now I’m happy if someone is able to help me up to this point as this is the crucial part I’m struggling with. However if you wish to help me on the following extra steps that would be amazing.
Okay so now if i do have a table like the one I’m after, is it possible to have an occurrence count in the last 12 months. So, let’s call our employee Jeff. He starts for my company on the 1st Jan 2020. Then on the 1st Feb he is ill for a week, that’s occurrence number 1 and he had had 5 days off. Then 1st November, he is ill again, this time for just one day. That’s occurrence number 2 and he has had 6 days off in total. Now Jeff is ill on 1st march 2021, he is now on occurrence number 2, as the time he was ill back in 1st Feb 2020 is now longer than 12 months ago, so it doesn’t count towards his sick anymore, but his one back in November is still within 12 months, so that one becomes occurrence number 1.
Now I’m ecstatic if you help me up to that point, however if you can solve this next one, you may be receiving my nomination for some sort of Nobel Prize.
Now let’s say Barry joined the company 5 years ago, and he has 2 days off in March, then 4 months later he has 3 days off, 4 months later he has 3 days off, 5 months later he has 3 days off, 4 months later he has another 2 days off. How would I go about predicting the probability Barry will be sick in the next 3 to 4 months is highly likely, say 90%, where as if he has just been sick, then the likelihood is much lower, getting more and more likely leading up to that 4 month point. And if he hasn't been sick after 4 months, he would be at 99% likely. I’m hoping to use this to roll up a sick forecast, so I know that as a whole, in the next month, I should expect 5 people to be off for about 100 hours, then in 2 months’ time, 10 people are predicted to be off equalling 200 hours, so that work can be planned accordingly. Again this one is not required and i will mark this post as solved once the 1st part of the question is answered.
Thank you
