7 day count and distinct count by worker in a calculated column

[domleps]

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.

[domleps]

this is amazing, thank you so much

[Jihwan_Kim · Accepted solution]

Link to the sample pbix file 

rolling 7 day absence_period COUNT by worker_id CC =

VAR _currentworker = Data[worker_id]

VAR _currentdate = Data[day worked]

VAR _sevendaystable =

FILTER (

Data,

Data[worker_id] = _currentworker

&& Data[day worked] <= _currentdate

&& Data[day worked] > _currentdate - 7

)

VAR _absensecolumnnoblank =

FILTER (

SELECTCOLUMNS ( _sevendaystable, "@absense", Data[absence_period_id] ),

[@absense] <> BLANK ()

)

RETURN

COUNTROWS ( _absensecolumnnoblank ) + 0

rolling 7 day absence_period DISTINCTCOUNT by worker_id CC =

VAR _currentworker = Data[worker_id]

VAR _currentdate = Data[day worked]

VAR _sevendaystable =

FILTER (

Data,

Data[worker_id] = _currentworker

&& Data[day worked] <= _currentdate

&& Data[day worked] > _currentdate - 7

)

VAR _absensecolumnnoblank =

SUMMARIZE (

FILTER (

SELECTCOLUMNS ( _sevendaystable, "@absense", Data[absence_period_id] ),

[@absense] <> BLANK ()

),

[@absense]

)

RETURN

COUNTROWS ( _absensecolumnnoblank ) + 0