cancel
Showing results 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

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:

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
Frequent Visitor

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``````

3 REPLIES 3
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]) ))

Frequent Visitor

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.

Frequent Visitor

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``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors