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

## Calculating average of last 10 values

Hello,

1. Average of last 10 business days values

2. Difference column - This column should calculate end of a rolling 10th business day value minus beginning of the rolling 10 business day

Please check attached sample data file. I have 2 different accounts and in dates weekends are excluded.

 Account Date Value Avg of last 10 business days Difference of 10BD minus 1BD 11111 1/1/2024 100 11111 1/2/2024 110 11111 1/3/2024 115 11111 1/4/2024 130 11111 1/5/2024 125 11111 1/8/2024 140 11111 1/9/2024 110 11111 1/10/2024 150 11111 1/11/2024 160 11111 1/12/2024 110 125 10 11111 1/15/2024 115 126.5 5 11111 1/16/2024 125 128 10 11111 1/17/2024 180 134.5 50 11111 1/18/2024 190 140.5 65 11111 1/19/2024 200 148 60 22222 1/1/2024 205 22222 1/2/2024 210 22222 1/3/2024 200 22222 1/4/2024 220 22222 1/5/2024 250 22222 1/8/2024 260 22222 1/9/2024 280 22222 1/10/2024 270 22222 1/11/2024 300 22222 1/12/2024 350 254.5 145 22222 1/15/2024 320 266 110 22222 1/16/2024 310 276 110 22222 1/17/2024 250 281 30 22222 1/18/2024 260 285 10 22222 1/19/2024 280 288 20
1 ACCEPTED SOLUTION
Community Support

Hi @san_21

Thanks for the reply from @amitchandak , please allow me to provide another insight:

Here I create two columns:

``````Avg_10 =
VAR _4days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 13
)
)
VAR _2days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 11
)
)
RETURN
IF (
_4days <> BLANK (),
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[Date]
>= EARLIER ( 'Table'[Date] ) - 13
)
),
IF (
_2days <> BLANK (),
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[Date]
>= EARLIER ( 'Table'[Date] ) - 11
)
)
)
)
``````
``````Diff =
VAR _4days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 13
)
)
VAR _2days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 11
)
)
RETURN
IF (
_4days <> BLANK (),
'Table'[Value] - _4days,
IF ( _2days <> BLANK (), 'Table'[Value] - _2days )
)
``````

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @san_21

Thanks for the reply from @amitchandak , please allow me to provide another insight:

Here I create two columns:

``````Avg_10 =
VAR _4days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 13
)
)
VAR _2days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 11
)
)
RETURN
IF (
_4days <> BLANK (),
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[Date]
>= EARLIER ( 'Table'[Date] ) - 13
)
),
IF (
_2days <> BLANK (),
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[Date]
>= EARLIER ( 'Table'[Date] ) - 11
)
)
)
)
``````
``````Diff =
VAR _4days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 13
)
)
VAR _2days =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Date]
= EARLIER ( 'Table'[Date] ) - 11
)
)
RETURN
IF (
_4days <> BLANK (),
'Table'[Value] - _4days,
IF ( _2days <> BLANK (), 'Table'[Value] - _2days )
)
``````

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi Amit,

Your formulas are for excluding weekend values? I have already exculded weekend values in transform data by adding Day of week column and filtering 6 & 0 value.

Super User

@san_21 , You can columns like these in your date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date cont Rank = if(isblank([Work Date] ), blank(), RANKX(ALL('Date'),[Work Date],,ASC,Dense) )

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

Avg Last 10 work day = CALCULATE(Averagex(Values(Date[Date]), calculate( sum('Table'[Qty]))) , FILTER(ALL('Date'), 'Date'[Work Date cont Rank]<=max('Date'[Work Date cont Rank]) && 'Date'[Work Date cont Rank]>=max('Date'[Work Date cont Rank])-10))

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

Power BI Workday vs Last Workday- https://youtu.be/MkYLT_GYIbM