Forum Discussion

ebrownretail's avatar
ebrownretail
Resolver I
2 years ago
Solved

Average sales for upcomign weeks

Hi!

I am new to Power BI and trying to build this to help manage inventory. We are a seasonal item, so we have very large humps in our sales progression during certain seasons. Due to this, i need to be able to pull the sales average from previous years, but i need this for upcoming weeks.

 

Example:

I need to make sure i have enough product to cover expected sales in the upcoming weeks. I need the average for the below week(s) to know what I need. Any ideas on how i can do this? I work with a 4-4-5 retail calendar so i do not have the same number of week each year.

 

Currently Week 24 - Need Week 25 avg + 26 avg for the previous years in my sales table.

Need Help
  • ebrownretail's avatar
    ebrownretail
    2 years ago

    Thank you for the reply! i was wondering if there is a way to do this without designating specific weeks? The week numbers will obviously change as the year progresses, and i dont want to have to edit the measure everytime.

     

    Is it possible to have this do all week numbers, and then i can narrow down with a slicer to the specific week numbers i want?

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ebrownretail ,

     

    I suggest you to try New Parameter feature to create a weeknum slicer.

    For reference: Use parameters to visualize variables - Power BI | Microsoft Learn

    Then update your measure as below.

    AverageWeek =
VAR _SELECTWEEK =
    SELETEDVALUE ( 'WeekNum'[Week] )
VAR _Avg1 =
    CALCULATE (
        AVERAGEX (
            VALUES ( DateTable[Year] ),
            CALCULATE ( [TotalSales], DateTable[Week] = _SELECTWEEK + 1 )
        ),
        ALL ( DateTable[Year] )
    )
VAR _Avg2 =
    CALCULATE (
        AVERAGEX (
            VALUES ( DateTable[Year] ),
            CALCULATE ( [TotalSales], DateTable[Week] = _SELECTWEEK + 2 )
        ),
        ALL ( DateTable[Year] )
    )
RETURN
    _Avg1 + _Avg2

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, ebrownretail 

    Ensure your sales data includes a date column, sales amount, and a column that identifies the week of the 4-4-5 calendar.
    Create a date table that follows the 4-4-5 calendar. This table should include columns for the date, year, week number, and any other relevant periods (e.g., quarter, month).

    DateTable = 
ADDCOLUMNS(
    CALENDAR("Start Date", "End Date"),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Week", WEEKNUM([Date])
)


    Create a relationship between your sales table and the date table based on the date columns.
    Calculate Weekly Sales:

    TotalSales = SUM(Sales[SalesAmount])

    Create a measure to calculate total sales for each week:

    AverageWeek25 = 
CALCULATE(
    AVERAGEX(
        VALUES(DateTable[Year]), 
        CALCULATE(
            [TotalSales], 
            DateTable[Week] = 25
        )
    ),
    ALL(DateTable[Year])
)

AverageWeek26 = 
CALCULATE(
    AVERAGEX(
        VALUES(DateTable[Year]), 
        CALCULATE(
            [TotalSales], 
            DateTable[Week] = 26
        )
    ),
    ALL(DateTable[Year])
)

    Combine the measures for forecasting the total required sales for the upcoming weeks:

    ForecastWeek25_26 = [AverageWeek25] + [AverageWeek26]

    Use a card or table visualization to display the forecasted sales for Week 25 and Week 26 based on the averages from previous years.

     

    If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

    • ebrownretail's avatar
      ebrownretail
      Resolver I
      2 years ago

      Thank you for the reply! i was wondering if there is a way to do this without designating specific weeks? The week numbers will obviously change as the year progresses, and i dont want to have to edit the measure everytime.

       

      Is it possible to have this do all week numbers, and then i can narrow down with a slicer to the specific week numbers i want?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi ebrownretail ,

         

        I suggest you to try New Parameter feature to create a weeknum slicer.

        For reference: Use parameters to visualize variables - Power BI | Microsoft Learn

        Then update your measure as below.

        AverageWeek =
VAR _SELECTWEEK =
    SELETEDVALUE ( 'WeekNum'[Week] )
VAR _Avg1 =
    CALCULATE (
        AVERAGEX (
            VALUES ( DateTable[Year] ),
            CALCULATE ( [TotalSales], DateTable[Week] = _SELECTWEEK + 1 )
        ),
        ALL ( DateTable[Year] )
    )
VAR _Avg2 =
    CALCULATE (
        AVERAGEX (
            VALUES ( DateTable[Year] ),
            CALCULATE ( [TotalSales], DateTable[Week] = _SELECTWEEK + 2 )
        ),
        ALL ( DateTable[Year] )
    )
RETURN
    _Avg1 + _Avg2

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.