How to Create a Weekly Forecast Model based on Lookup Table

Massingc1983 — Mon, 20 Nov 2023 19:57:49 GMT

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)

Re: How to Create a Weekly Forecast Model based on Lookup Table

123abc — Tue, 21 Nov 2023 05:44:16 GMT

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

Step 1: Data Preparation

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.
Relationships:
- Establish relationships between the tables based on common fields, such as project ID or project type.

Step 2: Create a Calendar Table

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

Project Start Date Variable:
- Ensure that the "project start date variable" is correctly calculated and linked to the calendar table.
Lookup Table Integration:
- Integrate the "project hrs Lookup" table into your model and connect it to the calendar table.

Step 4: Create Measures

Week Number Measure:
- Create a measure in the calendar table to calculate the week number based on the project start date.
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

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

Project Owner Filter:
- Add filters to your visuals to focus on specific project owners or other relevant criteria.
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

Test the Model:
- Ensure that your model is providing accurate forecasts and that the visuals are displaying the information you need.
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.

topic Re: How to Create a Weekly Forecast Model based on Lookup Table in DAX Commands and Tips