How to calculate rolling average for business days only.

[m_e_1203]

Hi, 

I am new to the PowerBI and strugling to calculate rolling 5 days average.

Sales date is only working days (no weekends and holidays), and quick measure won't work. 

Date (A)	Day of week (B)	Sales (C)	5 day moving average	formula I want to create (Exclude weekends and holiday)
8/31/2020	Mon	$           1,501	$                                           1,448	=AVERAGE(C2:C6)
8/28/2020	Fri	$           2,350	$                                           2,906	=AVERAGE(C3:C7)
8/27/2020	Thu	$           2,587	$                                           2,869	=AVERAGE(C4:C8)
8/26/2020	Wed	$              589	$                                           2,549	=AVERAGE(C5:C9)
8/25/2020	Tue	$              214	$                                           3,982
8/24/2020	Mon	$           8,791	$                                        12,933
8/21/2020	Fri	$           2,164	$                                        12,144
8/20/2020	Thu	$              986	$                                        14,442
8/19/2020	Wed	$           7,754	$                                        15,288
8/18/2020	Tue	$         44,968	$                                        14,931
8/17/2020	Mon	$           4,846	$                                           6,206
8/14/2020	Fri	$         13,657	$                                           5,555

I could use below if there is no holiday to calculate last 5 days of total sales. 

Rolling 5day Sales Date =

CALCULATE([Total Sales],

DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -7, DAY))

Please advice.

Thank you.

[amitchandak]

@m_e_1203 , I have blog for this. You need to create a rank on working day only. Then you can travel across working days

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

example

Plus 10 Days = var _max =maxx(ALLSELECTED('Date'),'Date'[Work Date cont Rank])
return
CALCULATE(Min('Date'[Date]),filter(ALL('Date'),'Date'[Work Date  Rank] =_max+10))

Rolling Last 10 Days =  CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Work Date cont Rank]>=min('Date'[Work Date cont Rank])-10 
				&& 'Date'[Work Date cont Rank]<=max('Date'[Work Date cont Rank])))

refer

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[m_e_1203]

@amitchandak 

Thank you for the message.

I was able to do first part. However, I could not get any numbers from below, All the results were blank.

Plus 10 Days = var _max =maxx(ALLSELECTED('Date'),'Date'[Work Date cont Rank])
return
CALCULATE(Min('Date'[Date]),filter(ALL('Date'),'Date'[Work Date cont Rank] =_max+10))

And this gave me same sales amount as daily total.

Rolling Last 10 Days = CALCULATE(SUM(Sales[SALES]),FILTER(all('Date'),'Date'[Work Date cont Rank]>=min('Date'[Work Date cont Rank])-10 
				&& 'Date'[Work Date cont Rank]<=max('Date'[Work Date cont Rank])))

Any addtional tips?

Thank you.

[Greg_Deckler]

@m_e_1203 - OK, turns out this is not so complicated. 2 columns. See below. PBIX is attached below sig. Table (30).

Column = COUNTROWS(FILTER('Table (30)',[Date (A)]<=EARLIER([Date (A)])))


5 Day Average = AVERAGEX(FILTER('Table (30)',[Column]>=EARLIER([Column])-4 && [Column]<=EARLIER([Column])),[Sales (C)])

[m_e_1203]

@Greg_Deckler  Thank you for your response.

I am getting an error.  What does this error mean?

Column is added in Data table, 5 Day Average is added in Sales table.

Sales Table contains multiple lines for single day sales. (Sales by business types.)

Column = COUNTROWS(FILTER('Date', 'Date'[Date] <=EARLIER('Date'[Date]) && 'Date'[If work day] = 1))

5 Day Average = AVERAGEX(FILTER('Date', 'Date'[Column] >= EARLIER ('Date'[Column]) -4 && 'Date'[Column]<=EARLIER('Date'[Column])),[Total Sales])

 Error :  EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

Thank you again!

[Greg_Deckler]

@m_e_1203  - My formula was for a column, seems like you were trying to use it in a measure. Do you want column or measure?

[m_e_1203]

---

@Greg_Deckler wrote:

@m_e_1203  - My formula was for a column, seems like you were trying to use it in a measure. Do you want column or measure?

---

@Greg_Deckler 

I am okay with either one as long as I can create a chart. I changed the name "Column" to "Work Date Cont Rank".

5 Day Average = AVERAGEX(FILTER('Date', 'Date'[Work Date cont Rank] >= EARLIER ([Work Date cont Rank]) -4 && 'Date'[Work Date cont Rank] <= EARLIER([Work Date cont Rank]), [Total Sales]))

 Error is "Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2."

Thank you again for your help.

[Greg_Deckler]

@m_e_1203 - Not sure why my attachment didn't show up. Table (30).

5 Day Average = 
  AVERAGEX(
    FILTER(
      'Date', 
      'Date'[Work Date cont Rank] >= EARLIER ([Work Date cont Rank]) -4 && 
      'Date'[Work Date cont Rank] <= EARLIER([Work Date cont Rank])
    ), 
    [Total Sales]
  )

You have a paren out of place. 

[m_e_1203]

@Greg_Deckler 

It worked!!   I really appretiate this.

5 Day Average_Date = 
  AVERAGEX(
    FILTER(
      'Date', 
      'Date'[Work Date cont Rank] >= EARLIER ([Work Date cont Rank]) -4 && 
      'Date'[Work Date cont Rank] <= EARLIER([Work Date cont Rank])
    ), 
    [Total Sales]
  )

But... One problem, it only calculated for total sales. When I add Business Type or Sales Location, it shows all  the same total.

Remove filters? 

5 Day AVG BusType = 
  CALCULATE(SUM('Date'[5 Day Average_Date]), REMOVEFILTERS(Business_Type[Business Type Group]))

Thanks again.

[Greg_Deckler · Accepted solution]

@m_e_1203 - Ah, if by add sales type or location you mean you are selecting those in a slicer, then you will need the measure form of this:

5 Day Average_Date Measure = 
    VAR __Max = MAX([Work Date cont Rank])
RETURN
    AVERAGEX(
        FILTER(
            ALLSELECTED('date'),
            [Work Date cont Rank]>=__Max-4 && 
            [Work Date cont Rank]<=__Max
        ),
        [Total Sales]
    )

[m_e_1203]

Worked!!

I have been trying this for last 2 weeks.  Great help!

Thank you so much!

[Greg_Deckler]

@m_e_1203 Yay!! 🙂

[m_e_1203]

When I add Row# to Sales Table, this is what it shows.

Row No = COUNTROWS(FILTER(Sales, Sales[EODDATE (Date)] <= EARLIER(Sales[EODDATE (Date)])))

Date:  RowNo

9/3:  63976

9/2:  63708

9/1:  63443

8/31:  63157 

Row No = COUNTROWS(FILTER('Date', 'Date'[Date] <= EARLIER(Sales[EODDATE (Date)])))

If I add this in Sales table using Date table's date, it shows like this.

Skipped Saturday and Sunday row number...

Date:  RowNo

9/3:  612

9/2:  611

9/1:  610

8/31:  609

8/28:  606

[Greg_Deckler]

@m_e_1203 - Seems like you would need a reverse Net Work Days to calculate the work day 5 work days ago and then the rest would be cake. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109