Average sales for upcomign weeks

[ebrownretail]

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.

[ebrownretail · Accepted solution]

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?

[hackcrr]

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 · Accepted solution]

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 · Accepted solution]

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.