- Data Modeling with DAX Tutorial
- Home
- Data Modeling with DAX - Overview
- Data Modeling with DAX - Concepts
- Data Modeling Using Excel Power Pivot
- Loading Data into the Data Model
- Defining Data Types in the Data Model
- Understanding Data Tables
- Extending the Data Model
- Base Finance Measures & Analysis
- YoY Finance Measures & Analysis
- Variance Measures & Analysis
- Year-to-Date Measures & Analysis
- Quarter-to-Date Measures & Analysis
- Budget Measures & Analysis
- Forecast Measures & Analysis
- Count of Months Measures
- Ending Headcount Measures
- Average Headcount Measures
- Total Headcount Measures
- YoY Headcount Measures & Analysis
- Variance Headcount Measures
- Cost Per Headcount Measures & Analysis
- Rate Variance & Volume Variance
- Data Modeling with DAX Resources
- Quick Guide
- Useful Resources
- Discussion
Forecast Measures and Analysis
You can use Forecast measures to analyze the finance data and help an organization make necessary adjustments in its goals and targets for the year, to align the company’s performance to the changing business requirements.
You need to update the forecasts regularly to keep up with the changes. You can then compare the most recent forecast to the budget for the rest of the period in the financial year so that the company can make the required adjustments to meet the business changes.
At any time during a financial year, you can calculate the following −
Forecast Attainment %
Forecast Attainment % is the percentage of the forecast sum that you have spent to date, i.e.
Forecast Attainment % = YTD Actual Sum/YTD Forecast Sum
Forecast Unexpended Balance
Forecast Unexpended Balance is the Forecast Sum remaining after the actual expenses, i.e
Forecast Unexpended Balance = YTD Forecast Sum – YTD Actual Sum
Budget Adjustment
Budget Adjustment is the adjustment in the budget sum an organization needs to make (an increase or decrease) based on the forecast.
Budget Adjustment = Forecast Unexpended Balance - Unexpended Balance
The budget needs to be increased if the resulting value is positive. Otherwise, it can be adjusted for some other purpose.
Creating Forecast Attainment Percentage Measure
You can create Forecast Attainment Percentage measure as follows −
Forecast Attainment Percentage:= IF([YTD Forecast Sum], [YTD Actual Sum]/[YTD Forecast Sum], BLANK())
Creating Forecast Unexpended Balance Measure
You can create Forecast Unexpended Balance measure as follows −
Forecast Unexpended Balance:=[YTD Forecast Sum]-[YTD Actual Sum]
Creating Budget Adjustment Measure
You can create Budget Adjustment measure as follows −
Budget Adjustment:=[Forecast Unexpended Balance]-[Unexpended Balance]
Analyzing Data with Forecast Measures
Create a Power PivotTable as follows −
Add Month from Date table to Rows.
Add the measures Budget Sum, YTD Budget Sum, YTD Actual Sum, Budget Attainment % and Unexpended Balance from Finance Data table to Values.
Insert a Slicer on Fiscal Year.
Select FY2016 in the Slicer.
