cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamalmsharma Frequent Visitor
Frequent Visitor

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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
kamalmsharma Frequent Visitor
Frequent Visitor

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

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. Smiley Happy

 

Best regards,

Kamal

5 REPLIES 5
Highlighted
Super User
Super User

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

Perhaps take a look at this and related articles:

 

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

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

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"

2.png

 

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
    )

3.png

Best Regards

Maggie

 

kamalmsharma Frequent Visitor
Frequent Visitor

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

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

kamalmsharma Frequent Visitor
Frequent Visitor

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

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. Smiley Happy

 

Best regards,

Kamal

kamalmsharma Frequent Visitor
Frequent Visitor

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

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. Smiley Happy

 

Best regards,

Kamal