Forum Discussion

Massingc1983's avatar
Massingc1983
Frequent Visitor
2 years ago

How to Create a Weekly Forecast Model based on Lookup Table

Hi 

 

I have a table "project hrs Lookup" which forecasts the weekly projected hours attributable to a project based on week number from project start date. 

 

I have created a Project Start & End Date Variables based on calculations against a contract end date in another table. This table also includes the project type, i.e. if it is NonStrategic, StrategicLow or StrategicHigh.

 

I want to be able to create a forecast and apply the below weekly values to each forecasted project from the Project Start Date Variable so i can ultimately wrap projects together based on project owner etc. 

 

Does anybody have any guidance on how i could achieve this?

 

Thank you 

 

Chris 

 

(table: project hrs Lookup)

 

 

 

 

1 Reply

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    To create a weekly forecast model based on a lookup table in Power BI, you can follow these general steps:

    Step 1: Data Preparation

    1. Data Sources:

      • Ensure that you have the necessary tables: "project hrs Lookup," the table with project start and end date variables, and any other relevant tables.

    2. Relationships:

      • Establish relationships between the tables based on common fields, such as project ID or project type.

    Step 2: Create a Calendar Table

    1. Calendar Table:
      • Create a calendar table that spans the time period you are forecasting. This table will be used to link to your other tables.

    Step 3: Model Your Data

    1. Project Start Date Variable:

      • Ensure that the "project start date variable" is correctly calculated and linked to the calendar table.

    2. Lookup Table Integration:

      • Integrate the "project hrs Lookup" table into your model and connect it to the calendar table.

    Step 4: Create Measures

    1. Week Number Measure:

      • Create a measure in the calendar table to calculate the week number based on the project start date.

    2. Forecast Measure:

      • Create a measure that looks up the forecasted hours from the "project hrs Lookup" table based on the week number.

    Step 5: Visualize Data

    1. Create Visuals:
      • Use Power BI visuals to display your forecasted hours. For example, use a line chart to show the trend of forecasted hours over time.

    Step 6: Filter and Aggregate

    1. Project Owner Filter:

      • Add filters to your visuals to focus on specific project owners or other relevant criteria.

    2. Aggregate Data:

      • Use aggregation functions to summarize data if needed, for example, to calculate total forecasted hours per project owner.

    Step 7: Test and Iterate

    1. Test the Model:

      • Ensure that your model is providing accurate forecasts and that the visuals are displaying the information you need.

    2. Iterate as Needed:

      • Adjust your measures or model structure if necessary based on your testing.

    Additional Tips:

    • DAX Functions:

      • Familiarize yourself with DAX (Data Analysis Expressions) functions, as they will be useful for creating calculated columns and measures.

    • Power BI Community:

      • If you encounter specific issues or have questions, the Power BI community is a valuable resource for finding solutions and getting assistance.

    Remember, the specific steps may vary based on the details of your data and requirements, but these general guidelines should help you get started with creating a weekly forecast model in Power BI.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.