Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mateja
Helper II
Helper II

Rolling 12 values when there is no rows present for the last month in rolling 12 period

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

Mateja_0-1674197757228.png

 

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

 

DateEmp idproject hours total hours
January 202285157.5157.5
February 202285150150
March 202285172.5172.5
April 20228593.7593.75
January 202285157.5157.5
February 202285150150
March 2022853030
January 202219168168
February 202219160160
March 202219184184
April 202219144144
May 202219176176
June 202219176176
July 202219168168
August 202219184184
September 202219176176
October 202219168168
November 202219176176
December 2022191616
September 202271125.5125.5
October 2022713737
January 2022717.5132
February 202271148148
March 202271148.5170.5
April 202271101133
May 202271145.5148.5
June 202271146.5155.5
July 2022718181
August 202271133.5133.5
September 202271140.5165.5
October 2022710176.5
November 2022710191
December 202271091.5
January 2022620157.5
February 2022621040.51069
March 2022620190.5
April 2022620130
May 2022620140.5
June 202262455548
July 202262027.5
August 2022620176.5
September 202262045.5
January 20228252185.5
February 20228259.5172
March 20228289189.5
April 20228222112
May 202282148.5154.5
June 202282155.5155.5
September 20228527.5104
October 202285139.5157.5
November 2022859.5154
December 20228526.582.5
January 202296168336
February 20229665130
March 202296103206
April 202296085.26
May 2022960125.86
June 202296120240
January 20223322123.75
February 2022331.0333388.51667
March 2022337.57.5
May 20223314.514.5
June 20223359.559.5
January 202255155.5155.5
February 202255148148
March 202255139.75171
April 20225570.25133
May 20225539.16666142.25
June 2022554.586
July 2022557.5141
August 202255091.5
September 2022550155.6167
October 2022550121.2333
November 2022550152.5833
December 2022550108
January 2022290155.5
February 2022290148
March 2022297170.5
April 2022290111
May 2022290148.5
June 2022290123
July 202229111155
August 202229170.5170.5
September 202229162.5162.5
October 2022291515
March 20226080158
April 2022600187.5
May 2022600282
June 2022600113
July 2022600199.5
August 202260168168
September 202260138138
October 2022603030
April 2022110105
May 2022112289
June 20221151.551.5
6 REPLIES 6
lbendlin
Super User
Super User

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

 

lbendlin_0-1674339729656.png

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 

DateEmployeeProject HoursTotal Hours
2021-01-01 00:00100189.5
2021-02-01 00:00100189.5
2021-03-01 00:00100186
2021-04-01 00:00100188.5
2021-05-01 00:0010029.6
2021-06-01 00:0010096.3
2021-07-01 00:00100127.5
2021-08-01 00:00100170
2021-09-01 00:00100210
2021-10-01 00:0010118118
2021-11-01 00:0010163163
2021-12-01 00:0010163163
2022-01-01 00:0010150155.5
2022-02-01 00:0010148148
2022-03-01 00:0010170.5170.5
2022-04-01 00:0010133133
2022-05-01 00:0010140148.5
2022-06-01 00:0010155.5155.5
2022-07-01 00:0010155155
2022-08-01 00:0010160170.5
2022-09-01 00:0010162.5162.5
2022-10-01 00:0010155.5155.5
2022-11-01 00:0010163163
2022-12-01 00:001080364
2021-01-01 00:00110169
2021-02-01 00:0011120195
2021-03-01 00:001194.594.5
2021-04-01 00:0011103.5111
2021-05-01 00:00110172
2021-06-01 00:00110197
2021-07-01 00:00110189
2021-08-01 00:00110193
2021-09-01 00:00110179.4
2021-10-01 00:0011089.6
2021-11-01 00:00110108
2021-12-01 00:00110121.4
2022-01-01 00:001170.5144
2022-02-01 00:00110267
2022-03-01 00:00110182
2022-04-01 00:0011142142
2022-05-01 00:00110200
2022-06-01 00:001144197
2022-07-01 00:00110201.5
2022-08-01 00:00110198
2022-09-01 00:0011126126
2022-10-01 00:001142150
2022-11-01 00:00110123.4
2022-12-01 00:00110304
2021-01-01 00:00120176.5
2021-02-01 00:00120194.4
2021-03-01 00:00120188
2021-04-01 00:0012089
2021-05-01 00:0012014.8
2021-06-01 00:001209
2021-07-01 00:00120138
2021-08-01 00:00120165.5
2021-09-01 00:00120203
2021-10-01 00:00128088.5
2021-11-01 00:00120194
2021-12-01 00:00120168
2022-01-01 00:00120137.5
2022-02-01 00:00120101
2022-03-01 00:00120148
2022-04-01 00:00120194.5
2022-05-01 00:00120200.5
2022-06-01 00:00120199
2022-07-01 00:00120181
2022-08-01 00:00120185.5
2022-09-01 00:00120113
2022-10-01 00:00120144
2022-11-01 00:00120142.5
2022-12-01 00:0012168168
2021-11-01 00:00140135.5
2021-12-01 00:00140268.5
2022-01-01 00:001436133.5
2021-01-01 00:00210160
2021-02-01 00:00210160
2021-03-01 00:0021040
2021-05-01 00:0021032
2021-06-01 00:00210112
2021-07-01 00:0021024
2021-08-01 00:00210154
2021-09-01 00:00210164.5
2021-10-01 00:00210236
2021-11-01 00:00210180
2021-12-01 00:0021056
2022-01-01 00:0021168168
2022-02-01 00:0021142162
2022-03-01 00:002168188
2022-04-01 00:00212138
2022-05-01 00:00210230.5
2022-06-01 00:0021092.5
2022-07-01 00:00210160.5
2022-08-01 00:00210238.5
2022-09-01 00:00210192.5
2022-10-01 00:00210106.5
2022-11-01 00:00210214.5
2022-12-01 00:00210226
2021-02-01 00:00330168
2021-03-01 00:00330193.5
2021-04-01 00:00330176.5
2021-05-01 00:00330168
2021-06-01 00:00330185.5
2021-07-01 00:00330180.5
2021-08-01 00:00330185
2021-09-01 00:00330185
2021-10-01 00:00330168
2021-11-01 00:003392172.5
2021-12-01 00:00330161
2022-01-01 00:00330172.25
2022-02-01 00:00330143
2022-03-01 00:00330186
2022-04-01 00:00330168
2022-05-01 00:00330118
2022-06-01 00:003317180.75
2022-07-01 00:00330176
2022-08-01 00:00330176.5
2022-09-01 00:003316.5184.5
2022-10-01 00:00330151.5
2022-11-01 00:003325195
2022-12-01 00:0033071.25
2021-01-01 00:00850132
2021-02-01 00:008522.5134
2021-03-01 00:00857152
2021-04-01 00:00850124.5
2021-05-01 00:008514133.5
2021-06-01 00:00857146
2021-07-01 00:00857145
2021-08-01 00:00857137
2021-09-01 00:0085091
2021-10-01 00:00857171.5
2021-11-01 00:00853.5143.5
2021-12-01 00:0085792
2022-01-01 00:00857121.5
2022-02-01 00:008514114
2022-03-01 00:00857158
2022-04-01 00:00857134.5
2022-05-01 00:00850131
2022-06-01 00:00850136
2022-07-01 00:008514132
2022-08-01 00:00854591
2022-09-01 00:00850150
2022-10-01 00:008544.5144
2022-11-01 00:00850114.5
2022-12-01 00:008522.5142.5
2021-01-01 00:00180160
2021-02-01 00:00180160
2021-03-01 00:00180144
2021-04-01 00:00180144
2021-05-01 00:00180155
2021-06-01 00:00180178
2021-07-01 00:00180131
2021-08-01 00:0018099
2021-09-01 00:00180146
2021-10-01 00:00180125
2022-01-01 00:0018168168
2022-02-01 00:0018160160
2022-03-01 00:0018184184
2022-04-01 00:0018144144
2022-05-01 00:0018176176
2022-06-01 00:0018176176
2022-07-01 00:0018168168
2022-08-01 00:0018184184
2022-09-01 00:0018176176
2022-10-01 00:0018168168
2022-11-01 00:0018176176
2022-12-01 00:00181616
2021-12-01 00:0019019.5
2022-01-01 00:00190228
2022-02-01 00:001948200.5
2022-03-01 00:001996223.5
2022-04-01 00:00190214
2022-05-01 00:00190237.5
2022-06-01 00:00190211
2022-07-01 00:00190189.5
2022-08-01 00:00190188.5
2022-09-01 00:00190189
2022-10-01 00:00190281.5
2022-11-01 00:00190286.5
2022-12-01 00:0019168254.5
2021-01-01 00:00230292.5
2021-02-01 00:00230103
2021-03-01 00:00230116
2021-04-01 00:00230280
2021-05-01 00:00230280
2021-06-01 00:00230151.5
2021-07-01 00:00230164
2021-08-01 00:00230267.5
2021-09-01 00:00230143.5
2021-10-01 00:00230155.5
2021-11-01 00:0023101206.5
2021-12-01 00:00230238
2022-01-01 00:00231252.5
2022-02-01 00:002384.5167
2022-03-01 00:002311195
2022-04-01 00:0023135.5135.5
2022-05-01 00:0023148.5148.5
2022-06-01 00:0023155.5155.5
2022-07-01 00:0023155155
2022-08-01 00:002374227.7
2022-09-01 00:00230187
2022-10-01 00:00230141.5
2022-11-01 00:0023128159
2022-12-01 00:002348169.5
2021-01-01 00:00920148
2021-02-01 00:00920135
2021-03-01 00:00920170.5
2021-04-01 00:00920133.5
2021-05-01 00:00920133.5
2021-06-01 00:00920163
2021-07-01 00:0092088.5
2021-08-01 00:00920126
2021-09-01 00:00920156
2021-10-01 00:00920137
2021-11-01 00:00927.5161.5
2021-12-01 00:00920123
2022-01-01 00:00920154
2022-02-01 00:00920148
2022-03-01 00:00920133.5
2022-04-01 00:00920133
2022-05-01 00:00920139.5
2022-06-01 00:00920155.5
2022-07-01 00:0092066
2022-08-01 00:00920156
2022-09-01 00:00920157.5
2022-10-01 00:00920155.5
2022-11-01 00:009215160
2022-12-01 00:009214.5148
2021-01-01 00:00630156
2021-02-01 00:00630179.5
2021-03-01 00:00630179
2021-04-01 00:006322.5148
2021-05-01 00:0063157.5157.5
2021-06-01 00:0063165165
2021-07-01 00:0063165165
2021-08-01 00:0063165165
2021-09-01 00:0063165165
2021-10-01 00:0063157.5157.5
2021-11-01 00:0063165165
2021-12-01 00:0063172.5172.5
2022-01-01 00:0063157.5157.5
2022-02-01 00:0063150150
2022-03-01 00:0063172.5172.5
2022-04-01 00:006393.7593.75
2021-01-01 00:006322.5150.5
2021-02-01 00:00630145.5
2021-03-01 00:0063127.5172.5
2021-04-01 00:0063165165
2021-05-01 00:0063157.5157.5
2021-06-01 00:0063165165
2021-07-01 00:0063164.5164.5
2021-08-01 00:0063165165
2021-09-01 00:0063165165
2021-10-01 00:0063157.5157.5
2021-11-01 00:0063165165
2021-12-01 00:0063172.5172.5
2022-01-01 00:0063157.5157.5
2022-02-01 00:0063150150
2022-03-01 00:00633030

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:

Mateja_0-1675003709507.png

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.