Pivot for SLA per week

[lakibalazs]

Hi,

I have a data source with daily entries for individuals, whether they come to office or not on the given day.

Each person is either billable or shadow, which can change with time. Besides date I have also have week# for convinience.

Date	Name	In office?	Week Number	Shadow/Billable
2024-01-01	AA BB	FALSE	1	Billable
2024-01-02	AA BB	FALSE	1	Billable
2024-01-03	AA BB	TRUE	1	Billable
2024-01-04	AA BB	TRUE	1	Billable
2024-01-05	AA BB	FALSE	1	Billable
2024-01-06	AA BB	FALSE	1	Billable
2024-01-07	AA BB	FALSE	2	Billable
2024-01-08	AA BB	FALSE	2	Billable
2024-01-09	AA BB	FALSE	2	Billable
2024-01-10	AA BB	FALSE	2	Billable
2024-01-11	AA BB	TRUE	2	Billable
2024-01-12	AA BB	FALSE	2	Billable
2024-01-13	AA BB	FALSE	2	Billable
2024-01-14	AA BB	FALSE	3	Billable
2024-01-15	AA BB	FALSE	3	Billable
2024-01-16	AA BB	FALSE	3	Billable
2024-01-17	AA BB	TRUE	3	Billable
2024-01-18	AA BB	TRUE	3	Billable
2024-01-19	AA BB	FALSE	3	Billable
2024-01-20	AA BB	FALSE	3	Billable
2024-01-01	AA CC	FALSE	1	Billable
2024-01-02	AA CC	TRUE	1	Billable
2024-01-03	AA CC	TRUE	1	Billable
2024-01-04	AA CC	TRUE	1	Billable
2024-01-05	AA CC	FALSE	1	Billable
2024-01-06	AA CC	FALSE	1	Billable
2024-01-07	AA CC	FALSE	2	Billable
2024-01-08	AA CC	FALSE	2	Billable
2024-01-09	AA CC	FALSE	2	Billable
2024-01-10	AA CC	FALSE	2	Billable
2024-01-11	AA CC	FALSE	2	Billable
2024-01-12	AA CC	FALSE	2	Billable
2024-01-13	AA CC	FALSE	2	Billable
2024-01-14	AA CC	FALSE	3	Billable
2024-01-15	AA CC	FALSE	3	Billable
2024-01-16	AA CC	FALSE	3	Billable
2024-01-17	AA CC	FALSE	3	Billable
2024-01-18	AA CC	FALSE	3	Billable
2024-01-19	AA CC	FALSE	3	Billable
2024-01-20	AA CC	FALSE	3	Billable
2024-01-01	AA DD	FALSE	1	Shadow
2024-01-02	AA DD	FALSE	1	Shadow
2024-01-03	AA DD	TRUE	1	Shadow
2024-01-04	AA DD	FALSE	1	Shadow
2024-01-05	AA DD	FALSE	1	Shadow
2024-01-06	AA DD	FALSE	1	Shadow
2024-01-07	AA DD	FALSE	2	Shadow
2024-01-08	AA DD	FALSE	2	Shadow
2024-01-09	AA DD	FALSE	2	Shadow
2024-01-10	AA DD	FALSE	2	Shadow
2024-01-11	AA DD	FALSE	2	Shadow
2024-01-12	AA DD	FALSE	2	Shadow
2024-01-13	AA DD	FALSE	2	Shadow
2024-01-14	AA DD	FALSE	3	Shadow
2024-01-15	AA DD	FALSE	3	Shadow
2024-01-16	AA DD	TRUE	3	Billable
2024-01-17	AA DD	TRUE	3	Billable
2024-01-18	AA DD	FALSE	3	Billable
2024-01-19	AA DD	FALSE	3	Billable
2024-01-20	AA DD	FALSE	3	Billable

From the above table I'd like to create a pivot, where I need to count the number of individuals who were in the office equal or more than two times. It is a kind of SLA. I need to devide the individuals into Billable/Shadow categories and report separately:

	Week	1	2	3
Billable	InOffice>=2	2	0	2
	InOffice<=1	0	2	1
Shadow	InOffice>=2	0	0	0
	InOffice<=1	1	1	0

Please help me in finding a good solution! Thank you!

[lakibalazs]

Thank you! Perfect Solution!

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@lakibalazs

you can try to create a column

InOffice =

VAR _count=countx(FILTER('Table','Table'[Week Number]=EARLIER('Table'[Week Number])&&'Table'[Shadow/Billable]=EARLIER('Table'[Shadow/Billable])&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[In office?]=true()),'Table'[Date])

return if(_count>=2,"InOffice>=2","InOffice<=1")

then create a matrix

pls see the attachment below