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:

 

DateArticleConsumption
2022-06-01A5
2022-05-03A4
2022-06-03B2
2022-06-01B4
2022-05-15B5
2022-06-04A3

 

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

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

1 Reply

  • 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