Weekly average based on previous six months data

[vipett]

I have a table that basically looks liks this:

Date	Article	Consumption
2022-06-01	A	5
2022-05-03	A	4
2022-06-03	B	2
2022-06-01	B	4
2022-05-15	B	5
2022-06-04	A	3

I would like to have a calculated column where I get the average consumption by week and only based on the previous six months data. It should be separated by article and article

[v-shex-msft · Accepted solution]

HI @vipett,

Did you mean to get the weekly total and average of rolling six months? If that is the case, you can try to use the following formula:

Formula =
AVERAGEX (
    SUMMARIZE (
        ADDCOLUMNS (
            FILTER (
                Table,
                [Date]
                    >= DATE ( YEAR ( EARLIER ( Table[Date] ) ), MONTH ( EARLIER ( Table[Date] ) ) - 6, DAY ( EARLIER ( Table[Date] ) ) )
                    && [Date] <= EARLIER ( Table[Date] )
                    && [Article] = EARLIER ( Table[Article] )
            ),
            "WeekNumber", WEEKNUM ( [Date], 2 ),
            "Year", YEAR ( [Date] )
        ),
        [Year],
        [WeekNumber],
        "Total", SUM ( Table[Consumption] )
    ),
    [Total]
)

Regards,

Xiaoxin Sheng