Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kamalmsharma
Helper I
Helper I

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

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

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

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

 

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

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
Super User
Super User

Perhaps take a look at this and related articles:

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.