Hi there,
I have the dataset shown below. This shows a list of workers (worker_id) and the days they are due to work (day_worked) - where a worker has not worked due to absence, they will have an absence_period_id.
I need to create two calculated columns in power bi that calculate the columns with red text below.
Please note this must be done in a calculated column and not in a measure, and I am not able to change the structure of this dataset. The dates may also have gaps (e.g. may not be a row for each date of the year).
| worker_id | day worked | absence_period_id | rolling 7 day absence_period COUNT by worker_id | rolling 7 day absence_period DISTINCTCOUNT by worker_id |
| A | 01/04/2021 | | 0 | 0 |
| A | 02/04/2021 | | 0 | 0 |
| A | 03/04/2021 | | 0 | 0 |
| A | 04/04/2021 | | 0 | 0 |
| A | 05/04/2021 | | 0 | 0 |
| A | 06/04/2021 | 1 | 1 | 1 |
| A | 07/04/2021 | 1 | 2 | 1 |
| A | 08/04/2021 | 1 | 3 | 1 |
| A | 09/04/2021 | | 3 | 1 |
| A | 10/04/2021 | | 3 | 1 |
| A | 11/04/2021 | 2 | 4 | 2 |
| A | 12/04/2021 | 2 | 5 | 2 |
| A | 13/04/2021 | | 4 | 2 |
| A | 14/04/2021 | | 3 | 2 |
| A | 15/04/2021 | 5 | 3 | 2 |
| A | 16/04/2021 | | 3 | 2 |
| A | 17/04/2021 | 3 | 4 | 3 |
| A | 18/04/2021 | | 3 | 3 |
| A | 19/04/2021 | | 2 | 2 |
| A | 20/04/2021 | 4 | 3 | 3 |
| A | 21/04/2021 | 4 | 4 | 3 |
| A | 22/04/2021 | | 3 | 2 |
| A | 23/04/2021 | | 3 | 2 |
| A | 24/04/2021 | | 2 | 1 |
| A | 25/04/2021 | | 2 | 1 |
| A | 26/04/2021 | | 2 | 1 |
| A | 27/04/2021 | | 1 | 1 |
| A | 28/04/2021 | 7 | 1 | 1 |
| A | 29/04/2021 | | 1 | 1 |
| B | 01/04/2021 | | 0 | 0 |
| B | 02/04/2021 | 6 | 1 | 1 |
| B | 03/04/2021 | 6 | 2 | 1 |
| B | 04/04/2021 | 6 | 3 | 1 |
| B | 05/04/2021 | | 3 | 1 |
| B | 06/04/2021 | | 3 | 1 |
| B | 07/04/2021 | 8 | 4 | 2 |
| B | 08/04/2021 | 8 | 5 | 2 |
| B | 09/04/2021 | 8 | 5 | 2 |
| B | 10/04/2021 | 8 | 5 | 2 |
| B | 11/04/2021 | 8 | 5 | 1 |
| B | 12/04/2021 | 8 | 6 | 1 |
| B | 13/04/2021 | | 6 | 1 |
| B | 14/04/2021 | | 5 | 1 |
| B | 15/04/2021 | | 4 | 1 |
| B | 16/04/2021 | 9 | 4 | 2 |
| B | 17/04/2021 | | 3 | 2 |
| B | 18/04/2021 | | 2 | 2 |
| B | 19/04/2021 | 10 | 2 | 2 |
| B | 20/04/2021 | 10 | 3 | 2 |
| B | 21/04/2021 | 11 | 4 | 3 |
| B | 22/04/2021 | | 4 | 3 |
| B | 23/04/2021 | | 3 | 2 |
| B | 24/04/2021 | | 3 | 2 |
| B | 25/04/2021 | 12 | 4 | 3 |
| B | 26/04/2021 | 12 | 4 | 3 |
| B | 27/04/2021 | 12 | 4 | 2 |
| B | 28/04/2021 | 12 | 4 | 1 |
| B | 29/04/2021 | 12 | 5 | 1 |
e.g. so if we look at date 25/04/2021 for worker B:
rolling 7 day absence_period COUNT by worker_id = count where absence_period_id <> blank between 19/04/2021 and 25/04/2021 (7 day period) where worker_id = B. This is 4 (absence_period_id = two 10s, one 11, one 12)
rolling 7 day absence_period DISTINCTCOUNT by worker_id = distinct count where absence_period_id <> blank between 19/04/2021 and 25/04/2021 (7 day period) where worker_id = B. This is 3 (absence_period_id = 10, 11, 12)
I hope that makes sense and thank you for your help.
