Forum Discussion

ebrownretail's avatar
ebrownretail
Icon for Resolver I rankResolver 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 t...
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.

hackcrr's avatar
hackcrr
Icon for Memorable Member rankMemorable 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
    Icon for Resolver I rankResolver 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.