Forum Discussion
Solved
Pivot for SLA per week
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!
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 matrixpls see the attachment below
3 Replies
ryan_mayuSuper User
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 matrixpls see the attachment belowThank you! Perfect Solution!
- ryan_mayu
you are welcome
