Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Rolling next 8-week average

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...
Need Help
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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.