Calculating average of last 10 values

[san_21]

Hello,

Can someone please help me with a DAX query for creating 2 new columns -

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

[v-zhengdxu-msft · Accepted solution]

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.

[san_21]

Hi Amit, 

Thanks for your reply.

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.

[amitchandak]

@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