Skip to main content
cancel
Showing results for 
Search instead 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

Reply
san_21
Frequent Visitor

Calculating average of last 10 values

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. 

 

AccountDateValueAvg of last 10 business daysDifference of 10BD minus 1BD
111111/1/2024100  
111111/2/2024110  
111111/3/2024115  
111111/4/2024130  
111111/5/2024125  
111111/8/2024140  
111111/9/2024110  
111111/10/2024150  
111111/11/2024160  
111111/12/202411012510
111111/15/2024115126.55
111111/16/202412512810
111111/17/2024180134.550
111111/18/2024190140.565
111111/19/202420014860
222221/1/2024205  
222221/2/2024210  
222221/3/2024200  
222221/4/2024220  
222221/5/2024250  
222221/8/2024260  
222221/9/2024280  
222221/10/2024270  
222221/11/2024300  
222221/12/2024350254.5145
222221/15/2024320266110
222221/16/2024310276110
222221/17/202425028130
222221/18/202426028510
222221/19/202428028820
1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
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:

vzhengdxumsft_0-1717394461732.png

 

 

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.

View solution in original post

3 REPLIES 3
v-zhengdxu-msft
Community Support
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:

vzhengdxumsft_0-1717394461732.png

 

 

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

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
Super User
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

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors