How to create weekly forecast on the basis of historical value of dynamically select week

[kamalmsharma]

Hi team,

I am trying to create weekly sales forecast using:

1. Historical value for the selected week as a reference. I should be able to change the historical week to change the reference.

2. Apply seasonality factor on the forecast.

For example, if the reference week is week 40 and sale was 500 units in that week, then I shall be able to forecast sales value for future weeks (week 52 of 2018 and onwards) by multiplying weekly seasonality factor to 500.

I am struggling with the first part. I think the second part will be easy if the former is fixed.

Any guidance is highly appreciated.

Best regards,

Kamal 

[v-juanli-msft]

Hi @kamalmsharma

Per your requirement, i make a test to achieve this.

if there is any difference between my test and your scenario, please let me know.

Dataset:

"historical data" table, "calendar" table, "factor" table, 

create two new tables

"slicer_weeknum", "slicer_year"

Create measures in "historical data" table

weekly_value =
CALCULATE (
    SUM ( 'historical data'[value] ),
    FILTER (
        ALL ( 'historical data' ),
        'historical data'[weeknum] = MAX ( 'historical data'[weeknum] )
    )
)

selected_year = SELECTEDVALUE(slicer_year[slicer_year])

selected_week = SELECTEDVALUE(slicer_weeknum[slicer_weeknum])

selected_week_value =
CALCULATE (
    [weekly_value],
    FILTER (
        ALL ( 'historical data' ),
        'historical data'[year] = [selected_year]
            && 'historical data'[weeknum] = [selected_week]
    )
)

Create calculated columns in "Calendar" table

Calendar_Year = YEAR('calendar'[Date])

Calendar_week = WEEKNUM('calendar'[Date],2)

merge1 = CONCATENATE([Calendar_Year],[Calendar_week])

create calcualted column in "factor" table

merge = CONCATENATE(factor[year],factor[week])

create relationship between "Calendar" table and "factor" table based on "merge1" and "merge" columns as the first screenshot

 finally, create measures in "Calendar" table

seasonality_factor =
LOOKUPVALUE (
    factor[seasonality factor],
    factor[year], MAX ( 'calendar'[Calendar_Year] ),
    factor[week], MAX ( 'calendar'[Calendar_week] )
)

his+for =
VAR historical_data =
    CALCULATE (
        SUM ( 'historical data'[value] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[Calendar_week] = MAX ( 'calendar'[Calendar_week] )
        )
    )
RETURN
    IF (
        historical_data = BLANK (),
        [seasonality_factor] * [selected_week_value],
        historical_data
    )

Best Regards

Maggie

[kamalmsharma · Accepted solution]

Hi Maggie,

Thank you so much for creating the test. I was able to solve the problem by creating two unrelated tables. One as copy for sales table and other for seasonality factors. Then I used slicer of weeks column of sales table to get the average sale for selected weeks. As these tables were not related, the slicer did not affect the forecast table visual.

My next struggle is with inventory forecast, for which I will upload a question shortly. 🙂

Best regards,

Kamal

[kamalmsharma]

Hi Maggie,

Thank you so much for creating the test. I was able to solve the problem by creating two unrelated tables. One as copy for sales table and other for seasonality factors. Then I used slicer of weeks column of sales table to get the average sale for selected weeks. As these tables were not related, the slicer did not affect the forecast table visual.

My next struggle is with inventory forecast, for which I will upload a question shortly. 🙂

Best regards,

Kamal

[Greg_Deckler]

Perhaps take a look at this and related articles:

https://community.powerbi.com/t5/Quick-Measures-Gallery/De-Seasonalized-Correlation-Coefficient/m-p/...

[kamalmsharma]

Hi Greg, thank you for sharing. I was able to solve the problem by creating a seasonality factors table and a copy of the sales table. I did not create any relationship between these tables so the slicer of sales table (to calculate average sales over selected weeks) did not affect the other forecast table visual.

Regards,

Kamal