7 Day Rolling Average excluding days not worked

[JWPowder]

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: 

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. 

[amitchandak]

@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]) ))

[JWPowder]

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)

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: 

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. 

[JWPowder · 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