Help with a measure for Rolling average over 3 days (exclude weekends)

[KS_learner]

Hi,

I need to build a rolling 3 day average of sales. The issue I'm facing is I need to exclude weekends in calculating my rolling average. So when I use datesinperiod in my measure my Monday & Tuesday measures get distorted.

This is my measure:

var NumDays = 3

var RollingSum =

CALCULATE(

sum(Data[USD]),

DATESINPERIOD(Data[Reporting Date],LASTDATE(Data[Reporting Date]),-3,day)

)

return

RollingSum/NumDays

Can someone help me work out this measure.

My data table is as below:

I have created an index (ranking table) for the reporting dates. But am not quite sure how to use that in this measure

[v-yalanwu-msft · Accepted solution]

Hi, @KS_learner ,

Try it.

measure = 
VAR NumDays = 3

var _sum=
IF(WEEKDAY(MAX('Data'[Date]),2)=6,CALCULATE(SUM([value]),FILTER(ALL(Data),DATEDIFF([Date],MAX('Data'[Date]),DAY) in {3,1,2})),
IF(WEEKDAY(MAX('Data'[Date]),2) in {7,1,2},CALCULATE(SUM([value]),FILTER(ALL(Data),DATEDIFF([Date],MAX('Data'[Date]),DAY) in {0,1,2,3,4}&&NOT(WEEKDAY([Date],2))in {6,7})),
CALCULATE(SUM([value]),FILTER(ALL(Data),DATEDIFF([Date],MAX('Data'[Date]),DAY) in {0,1,2}))))
 return _sum/NumDays

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KS_learner , You need to create a column Work Rank and use that

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)

Last 3 rooling work day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]>=max('Date'[Work Date cont Rank])-2 && 'Date'[Work Date cont Rank]<=max('Date'[Work Date cont Rank]) ))

use -3 if need

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...