Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create a rolling 3 period consecutive measure

I have a difficult measure I'm trying to build.  It relates to employees billing hours to timesheets.  We need to flag employees who bill more than 19 hours for 3 consecutive weeks.  Take a look at the following screenshot:

 

 

I've highlighted what I need to be calculated in yellow.  Note that for employee 73 and 1523, they have 4 consequtive periods billing more than 19 hours, so week 3 and 4 of that block would report a 1.  But note that you can't do a rolling average beacuse employee 1279 should not have any value in the week of Jan 16.  The other requirement is I need the count of employees to roll up as shown in bottom row.  (It would obviously be more than 1, 2, and 1 because the table is large.  But based on what is visible and what I marked up, it should be those values.)

 

Hopefully that gives you clear enough requirements.  Let me know if you have any questions.  Here is a onedrive link to a sample powerbi workbook you can start with.  Thanks in advance!

 

 

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks Amit.  I've spent about an hour trying to implement your suggestion without any luck.  One tricky part of my use case is the I need to go back 3 week periods, not months as your youtube video outlined.  (I am able to add values to my date table easily, I just trimmed everything down for this example to make it easier for others to jump in.)

       

      If you have time to download my workbook and play around with my model, I'd greatly appreciate it.