In other words, in order to show the forecast of a subcategory, we reallocate the budget defined at the category level by the percentage of sales of the given subcategory against the corresponding category in the previous year. When the forecast needs to be reallocated, we consider the sales in the previous year as an allocation factor. The following sections describe the business rules implemented in this pattern. Business choicesĪs with the model, we needed to set some business choices in order to author the DAX code.
2019 BI WEEKLY BUDGET CALENDAR TEMPLATE UPDATE
You should update the calculations to reflect your specific requirements and data model. We designed it to show you the DAX code, but different requirements might justify using a different model. This is not necessarily an optimal model.
Figure 3 For each new or dismissed product, the relevant information is stored in the Product table itself. Figure 3 shows the content of these three columns imported in Product from the Override table. Amount: the forecast sales over all countries for the first year.īecause the Override table has the same granularity as the Product table, we used Power Query to merge these three columns directly in the Product table.Year Del: the year a product was (or will be) dismissed.Year New: the year a new product was introduced.The relevant columns in the Override table are: The same Excel file includes another table called Override, which contains information about new and dismissed products. The forecast information of this pattern comes from an Excel file. The Scenario table implements the best practice of always using dimension tables to slice and dice, instead of using columns in the fact table ( Forecast in this case) for slicers and filters. A complete description of the MMR and SMR relationships in Power BI is available in the Relationships in Power BI and Tabular models article. NOTE The use of MMR and SMR is required to avoid confusion with other definitions of many-to-many relationships. At a more detailed (higher) granularity, they just repeat the total at the supported grain. SMR with Scenario based on the ScenarioĪll the MMRs are weak relationships they only filter at the granularity of the relationship.MMR with Store based on the CountryRegion.The following relationships start off of the Forecast table: Figure 2 The relationships between Forecast and other dimensions (other than Scenario) are weak MMR relationships. Consequently, the Forecast table is linked with the relevant tables using weak Many-Many-Relationships (MMR) and it has a Single-Many-Relationship (SMR) only with the Scenario table, as depicted in the diagram in Figure 2. There are no details about individual products, months or stores. Low granularity means that the information provided is at a very high level: year, store country, and product category. Therefore, the source data contains a forecast of sales for different scenarios at a low granularity. The scenario we are analyzing is a top-down forecasting scenario. The data modelīefore diving into the details of the calculations, it is important to make some considerations about the data model. The allocation of the new products by store country must be based on past sales of other products. The same Override table also includes the dismission date of the discontinued products that are not used in order to allocate the forecast. For this purpose, we use a table called Override that states when a product was introduced, along with the sales forecast for the first year. Correcting the forecast of future months based on how far they are from the actuals in past months of the current year.Īdditionally, we want to keep in mind new products that might be introduced throughout the years, as well as discontinued products for which the forecast should not be computed.Combining actuals and forecasts in the same report, using the actual values for the past months and the forecasts for the future months of the current year.
For example, computing the monthly forecast based on the sales in the previous year.
0 kommentar(er)
