Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I really hope you could help me with this one because I can't seem to find a viable solution.
I am suppose to calculate rolling 12 value for project hours compared to total hours (ratio or pct) on my Dashboard and show it on a graph that has different percentage classes: <5%, 5-10%, 10-25%, 25-50%, 50-75; 75-100% - I am basically counting employees who are in these different groups on the graph
These percentage values I also have in a dimensional table - picture below
The problem I stand in is that some employees do not have rows for all of the months in that period (because they stopped working for the company) and are therefore not showing on my graph.
Let say that I am calculating Rolling 12 for December 2022. There are some employees that stopped in October 2022 ( but basically anyone who stopped before December is a problem). If I make a calculated column for that data with IF condition it does not work because it is only showing the values on the graph for those who have a row in the December.
The point is - anyone who has had hours in the company in the last 12 months needs to be included in the calculation.
I have a date filter on my Dashboard so my collegues can switch between different months in different years.
I am attaching some of the mock up data with Date, emp id, project hours and total hours.
This table is conected to my Date table (that has all the days for the last 5 years) and that Date table is connected to the copy of the Date table with user relationship that I need for some othe calculations.
Do you know how can I solve this problem?
I can get the right calculations in a measure but I can not include the measure into the graph to group by the values in my dimensional table and I need to show it in a grapg
Date | Emp id | project hours | total hours |
January 2022 | 85 | 157.5 | 157.5 |
February 2022 | 85 | 150 | 150 |
March 2022 | 85 | 172.5 | 172.5 |
April 2022 | 85 | 93.75 | 93.75 |
January 2022 | 85 | 157.5 | 157.5 |
February 2022 | 85 | 150 | 150 |
March 2022 | 85 | 30 | 30 |
January 2022 | 19 | 168 | 168 |
February 2022 | 19 | 160 | 160 |
March 2022 | 19 | 184 | 184 |
April 2022 | 19 | 144 | 144 |
May 2022 | 19 | 176 | 176 |
June 2022 | 19 | 176 | 176 |
July 2022 | 19 | 168 | 168 |
August 2022 | 19 | 184 | 184 |
September 2022 | 19 | 176 | 176 |
October 2022 | 19 | 168 | 168 |
November 2022 | 19 | 176 | 176 |
December 2022 | 19 | 16 | 16 |
September 2022 | 71 | 125.5 | 125.5 |
October 2022 | 71 | 37 | 37 |
January 2022 | 71 | 7.5 | 132 |
February 2022 | 71 | 148 | 148 |
March 2022 | 71 | 148.5 | 170.5 |
April 2022 | 71 | 101 | 133 |
May 2022 | 71 | 145.5 | 148.5 |
June 2022 | 71 | 146.5 | 155.5 |
July 2022 | 71 | 81 | 81 |
August 2022 | 71 | 133.5 | 133.5 |
September 2022 | 71 | 140.5 | 165.5 |
October 2022 | 71 | 0 | 176.5 |
November 2022 | 71 | 0 | 191 |
December 2022 | 71 | 0 | 91.5 |
January 2022 | 62 | 0 | 157.5 |
February 2022 | 62 | 1040.5 | 1069 |
March 2022 | 62 | 0 | 190.5 |
April 2022 | 62 | 0 | 130 |
May 2022 | 62 | 0 | 140.5 |
June 2022 | 62 | 455 | 548 |
July 2022 | 62 | 0 | 27.5 |
August 2022 | 62 | 0 | 176.5 |
September 2022 | 62 | 0 | 45.5 |
January 2022 | 82 | 52 | 185.5 |
February 2022 | 82 | 59.5 | 172 |
March 2022 | 82 | 89 | 189.5 |
April 2022 | 82 | 22 | 112 |
May 2022 | 82 | 148.5 | 154.5 |
June 2022 | 82 | 155.5 | 155.5 |
September 2022 | 85 | 27.5 | 104 |
October 2022 | 85 | 139.5 | 157.5 |
November 2022 | 85 | 9.5 | 154 |
December 2022 | 85 | 26.5 | 82.5 |
January 2022 | 96 | 168 | 336 |
February 2022 | 96 | 65 | 130 |
March 2022 | 96 | 103 | 206 |
April 2022 | 96 | 0 | 85.26 |
May 2022 | 96 | 0 | 125.86 |
June 2022 | 96 | 120 | 240 |
January 2022 | 33 | 22 | 123.75 |
February 2022 | 33 | 1.03333 | 88.51667 |
March 2022 | 33 | 7.5 | 7.5 |
May 2022 | 33 | 14.5 | 14.5 |
June 2022 | 33 | 59.5 | 59.5 |
January 2022 | 55 | 155.5 | 155.5 |
February 2022 | 55 | 148 | 148 |
March 2022 | 55 | 139.75 | 171 |
April 2022 | 55 | 70.25 | 133 |
May 2022 | 55 | 39.16666 | 142.25 |
June 2022 | 55 | 4.5 | 86 |
July 2022 | 55 | 7.5 | 141 |
August 2022 | 55 | 0 | 91.5 |
September 2022 | 55 | 0 | 155.6167 |
October 2022 | 55 | 0 | 121.2333 |
November 2022 | 55 | 0 | 152.5833 |
December 2022 | 55 | 0 | 108 |
January 2022 | 29 | 0 | 155.5 |
February 2022 | 29 | 0 | 148 |
March 2022 | 29 | 7 | 170.5 |
April 2022 | 29 | 0 | 111 |
May 2022 | 29 | 0 | 148.5 |
June 2022 | 29 | 0 | 123 |
July 2022 | 29 | 111 | 155 |
August 2022 | 29 | 170.5 | 170.5 |
September 2022 | 29 | 162.5 | 162.5 |
October 2022 | 29 | 15 | 15 |
March 2022 | 60 | 80 | 158 |
April 2022 | 60 | 0 | 187.5 |
May 2022 | 60 | 0 | 282 |
June 2022 | 60 | 0 | 113 |
July 2022 | 60 | 0 | 199.5 |
August 2022 | 60 | 168 | 168 |
September 2022 | 60 | 138 | 138 |
October 2022 | 60 | 30 | 30 |
April 2022 | 11 | 0 | 105 |
May 2022 | 11 | 22 | 89 |
June 2022 | 11 | 51.5 | 51.5 |
Your sample data is a bit insufficient as it only covers 12 months of data. Therefore it's not possible to test the windowing aspect. Here is the basic approach
You would still have to apply the windowing and bucketing.
Hi Ibendlin
I actually need the graph to be divided by the categories on the picture provided in the post and not the months. Each category needs to contain counted values for the rolling 12 period meaning each employee who had some hours in that 12 window period need to be sorted in one of the categories.
I am attaching new sample data with longer periods
Date | Employee | Project Hours | Total Hours |
2021-01-01 00:00 | 10 | 0 | 189.5 |
2021-02-01 00:00 | 10 | 0 | 189.5 |
2021-03-01 00:00 | 10 | 0 | 186 |
2021-04-01 00:00 | 10 | 0 | 188.5 |
2021-05-01 00:00 | 10 | 0 | 29.6 |
2021-06-01 00:00 | 10 | 0 | 96.3 |
2021-07-01 00:00 | 10 | 0 | 127.5 |
2021-08-01 00:00 | 10 | 0 | 170 |
2021-09-01 00:00 | 10 | 0 | 210 |
2021-10-01 00:00 | 10 | 118 | 118 |
2021-11-01 00:00 | 10 | 163 | 163 |
2021-12-01 00:00 | 10 | 163 | 163 |
2022-01-01 00:00 | 10 | 150 | 155.5 |
2022-02-01 00:00 | 10 | 148 | 148 |
2022-03-01 00:00 | 10 | 170.5 | 170.5 |
2022-04-01 00:00 | 10 | 133 | 133 |
2022-05-01 00:00 | 10 | 140 | 148.5 |
2022-06-01 00:00 | 10 | 155.5 | 155.5 |
2022-07-01 00:00 | 10 | 155 | 155 |
2022-08-01 00:00 | 10 | 160 | 170.5 |
2022-09-01 00:00 | 10 | 162.5 | 162.5 |
2022-10-01 00:00 | 10 | 155.5 | 155.5 |
2022-11-01 00:00 | 10 | 163 | 163 |
2022-12-01 00:00 | 10 | 80 | 364 |
2021-01-01 00:00 | 11 | 0 | 169 |
2021-02-01 00:00 | 11 | 120 | 195 |
2021-03-01 00:00 | 11 | 94.5 | 94.5 |
2021-04-01 00:00 | 11 | 103.5 | 111 |
2021-05-01 00:00 | 11 | 0 | 172 |
2021-06-01 00:00 | 11 | 0 | 197 |
2021-07-01 00:00 | 11 | 0 | 189 |
2021-08-01 00:00 | 11 | 0 | 193 |
2021-09-01 00:00 | 11 | 0 | 179.4 |
2021-10-01 00:00 | 11 | 0 | 89.6 |
2021-11-01 00:00 | 11 | 0 | 108 |
2021-12-01 00:00 | 11 | 0 | 121.4 |
2022-01-01 00:00 | 11 | 70.5 | 144 |
2022-02-01 00:00 | 11 | 0 | 267 |
2022-03-01 00:00 | 11 | 0 | 182 |
2022-04-01 00:00 | 11 | 142 | 142 |
2022-05-01 00:00 | 11 | 0 | 200 |
2022-06-01 00:00 | 11 | 44 | 197 |
2022-07-01 00:00 | 11 | 0 | 201.5 |
2022-08-01 00:00 | 11 | 0 | 198 |
2022-09-01 00:00 | 11 | 126 | 126 |
2022-10-01 00:00 | 11 | 42 | 150 |
2022-11-01 00:00 | 11 | 0 | 123.4 |
2022-12-01 00:00 | 11 | 0 | 304 |
2021-01-01 00:00 | 12 | 0 | 176.5 |
2021-02-01 00:00 | 12 | 0 | 194.4 |
2021-03-01 00:00 | 12 | 0 | 188 |
2021-04-01 00:00 | 12 | 0 | 89 |
2021-05-01 00:00 | 12 | 0 | 14.8 |
2021-06-01 00:00 | 12 | 0 | 9 |
2021-07-01 00:00 | 12 | 0 | 138 |
2021-08-01 00:00 | 12 | 0 | 165.5 |
2021-09-01 00:00 | 12 | 0 | 203 |
2021-10-01 00:00 | 12 | 80 | 88.5 |
2021-11-01 00:00 | 12 | 0 | 194 |
2021-12-01 00:00 | 12 | 0 | 168 |
2022-01-01 00:00 | 12 | 0 | 137.5 |
2022-02-01 00:00 | 12 | 0 | 101 |
2022-03-01 00:00 | 12 | 0 | 148 |
2022-04-01 00:00 | 12 | 0 | 194.5 |
2022-05-01 00:00 | 12 | 0 | 200.5 |
2022-06-01 00:00 | 12 | 0 | 199 |
2022-07-01 00:00 | 12 | 0 | 181 |
2022-08-01 00:00 | 12 | 0 | 185.5 |
2022-09-01 00:00 | 12 | 0 | 113 |
2022-10-01 00:00 | 12 | 0 | 144 |
2022-11-01 00:00 | 12 | 0 | 142.5 |
2022-12-01 00:00 | 12 | 168 | 168 |
2021-11-01 00:00 | 14 | 0 | 135.5 |
2021-12-01 00:00 | 14 | 0 | 268.5 |
2022-01-01 00:00 | 14 | 36 | 133.5 |
2021-01-01 00:00 | 21 | 0 | 160 |
2021-02-01 00:00 | 21 | 0 | 160 |
2021-03-01 00:00 | 21 | 0 | 40 |
2021-05-01 00:00 | 21 | 0 | 32 |
2021-06-01 00:00 | 21 | 0 | 112 |
2021-07-01 00:00 | 21 | 0 | 24 |
2021-08-01 00:00 | 21 | 0 | 154 |
2021-09-01 00:00 | 21 | 0 | 164.5 |
2021-10-01 00:00 | 21 | 0 | 236 |
2021-11-01 00:00 | 21 | 0 | 180 |
2021-12-01 00:00 | 21 | 0 | 56 |
2022-01-01 00:00 | 21 | 168 | 168 |
2022-02-01 00:00 | 21 | 142 | 162 |
2022-03-01 00:00 | 21 | 68 | 188 |
2022-04-01 00:00 | 21 | 2 | 138 |
2022-05-01 00:00 | 21 | 0 | 230.5 |
2022-06-01 00:00 | 21 | 0 | 92.5 |
2022-07-01 00:00 | 21 | 0 | 160.5 |
2022-08-01 00:00 | 21 | 0 | 238.5 |
2022-09-01 00:00 | 21 | 0 | 192.5 |
2022-10-01 00:00 | 21 | 0 | 106.5 |
2022-11-01 00:00 | 21 | 0 | 214.5 |
2022-12-01 00:00 | 21 | 0 | 226 |
2021-02-01 00:00 | 33 | 0 | 168 |
2021-03-01 00:00 | 33 | 0 | 193.5 |
2021-04-01 00:00 | 33 | 0 | 176.5 |
2021-05-01 00:00 | 33 | 0 | 168 |
2021-06-01 00:00 | 33 | 0 | 185.5 |
2021-07-01 00:00 | 33 | 0 | 180.5 |
2021-08-01 00:00 | 33 | 0 | 185 |
2021-09-01 00:00 | 33 | 0 | 185 |
2021-10-01 00:00 | 33 | 0 | 168 |
2021-11-01 00:00 | 33 | 92 | 172.5 |
2021-12-01 00:00 | 33 | 0 | 161 |
2022-01-01 00:00 | 33 | 0 | 172.25 |
2022-02-01 00:00 | 33 | 0 | 143 |
2022-03-01 00:00 | 33 | 0 | 186 |
2022-04-01 00:00 | 33 | 0 | 168 |
2022-05-01 00:00 | 33 | 0 | 118 |
2022-06-01 00:00 | 33 | 17 | 180.75 |
2022-07-01 00:00 | 33 | 0 | 176 |
2022-08-01 00:00 | 33 | 0 | 176.5 |
2022-09-01 00:00 | 33 | 16.5 | 184.5 |
2022-10-01 00:00 | 33 | 0 | 151.5 |
2022-11-01 00:00 | 33 | 25 | 195 |
2022-12-01 00:00 | 33 | 0 | 71.25 |
2021-01-01 00:00 | 85 | 0 | 132 |
2021-02-01 00:00 | 85 | 22.5 | 134 |
2021-03-01 00:00 | 85 | 7 | 152 |
2021-04-01 00:00 | 85 | 0 | 124.5 |
2021-05-01 00:00 | 85 | 14 | 133.5 |
2021-06-01 00:00 | 85 | 7 | 146 |
2021-07-01 00:00 | 85 | 7 | 145 |
2021-08-01 00:00 | 85 | 7 | 137 |
2021-09-01 00:00 | 85 | 0 | 91 |
2021-10-01 00:00 | 85 | 7 | 171.5 |
2021-11-01 00:00 | 85 | 3.5 | 143.5 |
2021-12-01 00:00 | 85 | 7 | 92 |
2022-01-01 00:00 | 85 | 7 | 121.5 |
2022-02-01 00:00 | 85 | 14 | 114 |
2022-03-01 00:00 | 85 | 7 | 158 |
2022-04-01 00:00 | 85 | 7 | 134.5 |
2022-05-01 00:00 | 85 | 0 | 131 |
2022-06-01 00:00 | 85 | 0 | 136 |
2022-07-01 00:00 | 85 | 14 | 132 |
2022-08-01 00:00 | 85 | 45 | 91 |
2022-09-01 00:00 | 85 | 0 | 150 |
2022-10-01 00:00 | 85 | 44.5 | 144 |
2022-11-01 00:00 | 85 | 0 | 114.5 |
2022-12-01 00:00 | 85 | 22.5 | 142.5 |
2021-01-01 00:00 | 18 | 0 | 160 |
2021-02-01 00:00 | 18 | 0 | 160 |
2021-03-01 00:00 | 18 | 0 | 144 |
2021-04-01 00:00 | 18 | 0 | 144 |
2021-05-01 00:00 | 18 | 0 | 155 |
2021-06-01 00:00 | 18 | 0 | 178 |
2021-07-01 00:00 | 18 | 0 | 131 |
2021-08-01 00:00 | 18 | 0 | 99 |
2021-09-01 00:00 | 18 | 0 | 146 |
2021-10-01 00:00 | 18 | 0 | 125 |
2022-01-01 00:00 | 18 | 168 | 168 |
2022-02-01 00:00 | 18 | 160 | 160 |
2022-03-01 00:00 | 18 | 184 | 184 |
2022-04-01 00:00 | 18 | 144 | 144 |
2022-05-01 00:00 | 18 | 176 | 176 |
2022-06-01 00:00 | 18 | 176 | 176 |
2022-07-01 00:00 | 18 | 168 | 168 |
2022-08-01 00:00 | 18 | 184 | 184 |
2022-09-01 00:00 | 18 | 176 | 176 |
2022-10-01 00:00 | 18 | 168 | 168 |
2022-11-01 00:00 | 18 | 176 | 176 |
2022-12-01 00:00 | 18 | 16 | 16 |
2021-12-01 00:00 | 19 | 0 | 19.5 |
2022-01-01 00:00 | 19 | 0 | 228 |
2022-02-01 00:00 | 19 | 48 | 200.5 |
2022-03-01 00:00 | 19 | 96 | 223.5 |
2022-04-01 00:00 | 19 | 0 | 214 |
2022-05-01 00:00 | 19 | 0 | 237.5 |
2022-06-01 00:00 | 19 | 0 | 211 |
2022-07-01 00:00 | 19 | 0 | 189.5 |
2022-08-01 00:00 | 19 | 0 | 188.5 |
2022-09-01 00:00 | 19 | 0 | 189 |
2022-10-01 00:00 | 19 | 0 | 281.5 |
2022-11-01 00:00 | 19 | 0 | 286.5 |
2022-12-01 00:00 | 19 | 168 | 254.5 |
2021-01-01 00:00 | 23 | 0 | 292.5 |
2021-02-01 00:00 | 23 | 0 | 103 |
2021-03-01 00:00 | 23 | 0 | 116 |
2021-04-01 00:00 | 23 | 0 | 280 |
2021-05-01 00:00 | 23 | 0 | 280 |
2021-06-01 00:00 | 23 | 0 | 151.5 |
2021-07-01 00:00 | 23 | 0 | 164 |
2021-08-01 00:00 | 23 | 0 | 267.5 |
2021-09-01 00:00 | 23 | 0 | 143.5 |
2021-10-01 00:00 | 23 | 0 | 155.5 |
2021-11-01 00:00 | 23 | 101 | 206.5 |
2021-12-01 00:00 | 23 | 0 | 238 |
2022-01-01 00:00 | 23 | 12 | 52.5 |
2022-02-01 00:00 | 23 | 84.5 | 167 |
2022-03-01 00:00 | 23 | 11 | 195 |
2022-04-01 00:00 | 23 | 135.5 | 135.5 |
2022-05-01 00:00 | 23 | 148.5 | 148.5 |
2022-06-01 00:00 | 23 | 155.5 | 155.5 |
2022-07-01 00:00 | 23 | 155 | 155 |
2022-08-01 00:00 | 23 | 74 | 227.7 |
2022-09-01 00:00 | 23 | 0 | 187 |
2022-10-01 00:00 | 23 | 0 | 141.5 |
2022-11-01 00:00 | 23 | 128 | 159 |
2022-12-01 00:00 | 23 | 48 | 169.5 |
2021-01-01 00:00 | 92 | 0 | 148 |
2021-02-01 00:00 | 92 | 0 | 135 |
2021-03-01 00:00 | 92 | 0 | 170.5 |
2021-04-01 00:00 | 92 | 0 | 133.5 |
2021-05-01 00:00 | 92 | 0 | 133.5 |
2021-06-01 00:00 | 92 | 0 | 163 |
2021-07-01 00:00 | 92 | 0 | 88.5 |
2021-08-01 00:00 | 92 | 0 | 126 |
2021-09-01 00:00 | 92 | 0 | 156 |
2021-10-01 00:00 | 92 | 0 | 137 |
2021-11-01 00:00 | 92 | 7.5 | 161.5 |
2021-12-01 00:00 | 92 | 0 | 123 |
2022-01-01 00:00 | 92 | 0 | 154 |
2022-02-01 00:00 | 92 | 0 | 148 |
2022-03-01 00:00 | 92 | 0 | 133.5 |
2022-04-01 00:00 | 92 | 0 | 133 |
2022-05-01 00:00 | 92 | 0 | 139.5 |
2022-06-01 00:00 | 92 | 0 | 155.5 |
2022-07-01 00:00 | 92 | 0 | 66 |
2022-08-01 00:00 | 92 | 0 | 156 |
2022-09-01 00:00 | 92 | 0 | 157.5 |
2022-10-01 00:00 | 92 | 0 | 155.5 |
2022-11-01 00:00 | 92 | 15 | 160 |
2022-12-01 00:00 | 92 | 14.5 | 148 |
2021-01-01 00:00 | 63 | 0 | 156 |
2021-02-01 00:00 | 63 | 0 | 179.5 |
2021-03-01 00:00 | 63 | 0 | 179 |
2021-04-01 00:00 | 63 | 22.5 | 148 |
2021-05-01 00:00 | 63 | 157.5 | 157.5 |
2021-06-01 00:00 | 63 | 165 | 165 |
2021-07-01 00:00 | 63 | 165 | 165 |
2021-08-01 00:00 | 63 | 165 | 165 |
2021-09-01 00:00 | 63 | 165 | 165 |
2021-10-01 00:00 | 63 | 157.5 | 157.5 |
2021-11-01 00:00 | 63 | 165 | 165 |
2021-12-01 00:00 | 63 | 172.5 | 172.5 |
2022-01-01 00:00 | 63 | 157.5 | 157.5 |
2022-02-01 00:00 | 63 | 150 | 150 |
2022-03-01 00:00 | 63 | 172.5 | 172.5 |
2022-04-01 00:00 | 63 | 93.75 | 93.75 |
2021-01-01 00:00 | 63 | 22.5 | 150.5 |
2021-02-01 00:00 | 63 | 0 | 145.5 |
2021-03-01 00:00 | 63 | 127.5 | 172.5 |
2021-04-01 00:00 | 63 | 165 | 165 |
2021-05-01 00:00 | 63 | 157.5 | 157.5 |
2021-06-01 00:00 | 63 | 165 | 165 |
2021-07-01 00:00 | 63 | 164.5 | 164.5 |
2021-08-01 00:00 | 63 | 165 | 165 |
2021-09-01 00:00 | 63 | 165 | 165 |
2021-10-01 00:00 | 63 | 157.5 | 157.5 |
2021-11-01 00:00 | 63 | 165 | 165 |
2021-12-01 00:00 | 63 | 172.5 | 172.5 |
2022-01-01 00:00 | 63 | 157.5 | 157.5 |
2022-02-01 00:00 | 63 | 150 | 150 |
2022-03-01 00:00 | 63 | 30 | 30 |
You have not indicated the expected result - do you want to show the number of employees falling into each bucket for each date and its lookback period?
My graph should have these 6 categories on the x -axis:
On y-axis I want to count employees according to these categories by dividing their project hours with their working hours.
Calculation needs to look back 12 months.
It needs to take into consideration all the employees who had Working hours in that period, even if they do not work for the company any more. IF I use the calculated column to solve this issue, then I am only showing the values or better said counting employees who are employed in the month that is set on my date filer. If I set in my date filter OCtober 2022, by using the calculated column to approach this problem, the graph will only show pople who are employed because they have a row for that month. I also want to show people who were working for a company in this 12-month period but they do not anymore.
For example a person started working for company in December 2021 but stoped in July 2022. In that period they had 960 working hours, of those 200 were project hours meaning they belong to the category 10% - 25% because 960/200=0.2083. I am interested in their ratio project hours vs working hours , so even if they do not work for the company any more, they are still imortant for this calculation.
Hopes this clears it a little bit!
Look back 12 months from where?
@tamerj1 Could you please look at this issue if you have time. I am also stuck on this problem.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |