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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JWPowder
Frequent Visitor

7 Day Rolling Average excluding days not worked

Hello, I am trying to create a 7 day rolling average while exluding the days where we didnt work. I am using the DAX Expression below:

 

7 Day Rolling Average = 
Calculate(Sum('Daily Production'[Lbs]),DATESINPERIOD('Daily Production'[Date_1], LASTDATE('Daily Production'[Date_1]),7,DAY))
/
Calculate(DISTINCTCOUNT('Daily Production'[Date_1]),DATESINPERIOD('Daily Production'[Date_1],LASTDATE('Daily Production'[Date_1]),7,DAY))

Screenshot of data: 

JWPowder_0-1686838549720.png

 

As you can see, the rolling average is factoring in the blank fields as well. I need help filtering this expresion to exclude rows where the is nothing in the "Worked" column. 

1 ACCEPTED SOLUTION

Hello @amitchandak , I was able to modify another expression I found on the forum and got the result I was looking for. 

 

7 Day rolling Average = 
var __workingDays = 7
var __currentDay = MAX( 'Daily Production'[Date Worked] )
var __daysToAvgOver =
	TOPN(__workingDays,
		CALCULATETABLE(
			'Daily Production',
			'Daily Production'[Date Worked] < __currentDay,
			'Daily Production'[Worked] = 1,
			ALL( 'Daily Production' )
		),
		'Daily Production'[Date Worked],
		DESC
	)
var __avg =
	DIVIDE(
		CALCULATE(
			SUM( 'Daily Production'[Lbs] ),
			__daysToAvgOver
		),
		COUNTROWS( __daysToAvgOver )
	)
return
	__avg

 

JWPowder_0-1686934373625.png

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@JWPowder , Assume you have employee and worked flag .

 

First create a rank

Rank WD = if([Worked] <> Blank(), Rankx(filter('Daily Production', [Employee] = earlier([Employee]) && [Worked] =1), [Date]), blank())

 

 

Create a measure like

Rolling 7 WD = CALCULATE(Averagex(Values('Daily Production'[Date]) ,calculate(Sum('Daily Production'[Lbs]))), WINDOW(-6,REL, 0, REL, ALLSELECTED('Daily Production'[Date] , 'Daily Production'[Employee] ),ORDERBY([Date],asc),,PARTITIONBY('Daily Production'[Employee]) ))

 

 

 

Hello @amitchandak , I do not have a column for employee. I created a rank column using the expression below:

 

 

Date Rank = 

var _rank=RANKX(FILTER('Daily Production',[Date Worked]<>BLANK()),[Date Worked],,ASC,Dense)

return

IF('Daily Production'[Worked]=BLANK(),BLANK(),_rank)

 

 

JWPowder_0-1686919211061.png

 

I modified the expression you suggested:

 

7 Day rolling Average = 
CALCULATE(Averagex(Values('Daily Production'[Date Worked]) ,calculate(Sum('Daily Production'[Lbs]))), 
WINDOW(-6,REL, 0, REL, ALLSELECTED('Daily Production'[Date Worked]),ORDERBY([Date Worked],asc)))

 

Result: 

JWPowder_1-1686919445282.png

EDIT** Forgot to mention that I would like to use a calculated column so I can use the data as a line value in a visualization. 

Hello @amitchandak , I was able to modify another expression I found on the forum and got the result I was looking for. 

 

7 Day rolling Average = 
var __workingDays = 7
var __currentDay = MAX( 'Daily Production'[Date Worked] )
var __daysToAvgOver =
	TOPN(__workingDays,
		CALCULATETABLE(
			'Daily Production',
			'Daily Production'[Date Worked] < __currentDay,
			'Daily Production'[Worked] = 1,
			ALL( 'Daily Production' )
		),
		'Daily Production'[Date Worked],
		DESC
	)
var __avg =
	DIVIDE(
		CALCULATE(
			SUM( 'Daily Production'[Lbs] ),
			__daysToAvgOver
		),
		COUNTROWS( __daysToAvgOver )
	)
return
	__avg

 

JWPowder_0-1686934373625.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors