Create a rolling 3 period consecutive measure

[bensycamore]

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!

[amitchandak]

@bensycamore , First create a measure like

if([hour measure]>19, 1, blank())

then follow the approch

Customer Purchasing in each of the last 3 months: https://youtu.be/bGtjoccEA38

[bensycamore]

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.

[milanpasschier3]

Hey man,

This one was challenging lol.

Check this: https://drive.google.com/drive/folders/1NioylLFTe9JpkwzhKVwwQlJdroLgf0JE?usp=sharing

Best,

Milan

PS. Thumbs up and mark as solution if it fits your needs.

[bensycamore]

Thanks Milan!  You got sooo close.  The part that is still lacking is the total lines at the bottom.  Your solution they are blank.  😞  

I think I'm going to do some data prep prior to arriving in PBI, that will keep my logic cleaner inside PBI.

Thanks for helping!

[milanpasschier3]

Hold tight. Will try to fix it.

[milanpasschier3 · Accepted solution]

Woopwoop, have a look in the folder now: https://drive.google.com/drive/folders/1BGo8s4skTTlKmUbdtvCpSGgp8i9RusNP?usp=sharing

Check the screenshot below:

[bensycamore]

You did it!  Thanks Milan

[milanpasschier3]

My pleasure man