Rolling next 8-week average

[Anonymous]

Hi all,

I was struggling to figure out how to get an average demand from next 8 weeks, if the remainging weeks are less than 8 weeks, then calculate average based on the number of remaining weeks.

Simplfied dataset as below.

Week	Demand	Avg_Demand	Remark
2023/1/9	65	64.875	(45+45+66+75+75+75+70+68)/8
2023/1/16	45	67.75	(45+66+75+75+75+70+68+68)/8
2023/1/23	45	71	(66+75+75+75+70+68+68)/7
2023/1/30	66	71.83333333	(75+75+75+70+68+68)/6
2023/2/6	75	71.2	(75+75+70+68+68)/5
2023/2/13	75	70.25	(75+70+68+68)/4
2023/2/20	75	68.66666667	(70+68+68)/3
2023/2/27	70	68	(68+68)/2
2023/3/6	68	68	68
2023/3/13	68

Thanks in advance!

[Padycosmos]

Hope this helps

[FreemanZ]

hi @Anonymous 

you may also try to add a column like this:

Avg_Demand2 = 
VAR _week = WEEKNUM([Week])
RETURN
AVERAGEX(
    FILTER(
        TableName, 
        WEEKNUM(TableName[Week])<=_week+8
            &&WEEKNUM(TableName[Week])>_week
    ),
    TableName[Demand]
)

it worked like this:

[Padycosmos]

Hope this helps

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but I assume you have a calendar table like below.

I tried to create a sample pbix file like below, and please check the below picure and the attached pbix file.

Demand measure: = 
SUM( Data[Demand] )

Expected result measure: =
VAR _condition =
    DIVIDE ( [Demand measure:], [Demand measure:] )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[Year-Wk] ),
        _condition
            * AVERAGEX (
                ADDCOLUMNS (
                    WINDOW (
                        1,
                        REL,
                        8,
                        REL,
                        ALL ( 'Calendar'[Year-Wk], 'Calendar'[Start of Week] )
                    ),
                    "@demand", [Demand measure:]
                ),
                [@demand]
            )
    )

[Anonymous]

Hi @FreemanZ , @Jihwan_Kim 

Thanks for answerning my question.

However, I'm not sure why both of them do not display as I wish..

Probably, a little more about the dataset would help. It demonstrates weekly demand for each product by different region and destination from 2017 to present. I'd like to get an average demand when I collapse or expand the matrix.

Really appreciate your help.