Forum Discussion

vipett's avatar
vipett
Icon for Helper III rankHelper III
4 years ago
Solved

Weekly average based on previous six months data

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 ...
Need Help DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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