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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
bensycamore
Frequent Visitor

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:

 

bensycamore_0-1683215398032.png

 

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!

 

 

1 ACCEPTED SOLUTION

8 REPLIES 8
amitchandak
Super User
Super User

@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

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.

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.

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!

Hold tight. Will try to fix it.

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

 

Check the screenshot below:

 

Screenshot 2023-05-05 160043.png

You did it!  Thanks Milan

My pleasure man

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.