Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I'm trying to build a table to compare how the forecast for a full year have been changing from month over month. It is called "waterfall" at my company. However, "waterfall" is not the name at the global community, so I haven't succeed in finding references on how to build it.
Example:
Green cells refer to actual sales and yellow cells to forecast. Each row shows the forecast/actual for the full year rolled out in a certain month (forecasting cycle).
I designed the report having a Dates Dimension table and two fact tables: Forecast and Actuals. Unfortunately, the outcome is not what I expected:
Does anyone knows if there is already a template/visual to accomplish this report?
I'd like to avoid developing queries/processes to calculate the full year estimate for each forecasting cycle a/o product.
Please advise.
Thank you.
Solved! Go to Solution.
Hi guys,
At last, I managed to build the report.
DATA TABLES
* dim_Dates. Date, Year, Year/Month
* dim_Forecast_Cycles. Cycle (Date), Year, Year/Month
* fact_Forecast. Cyce (Date), Forecasted Month (Date), Quantity.
* fact_Sales. Sales Date (Date), Quantity.
If you could group the information by month, the relationships and performance would be much easier.
RELATIONSHIPS
* fact_Forecast[Cycle] -> dim_Forecast_Cycle[Cycle]
* fact_Forecast[Forecasted Month] -> dim_Dates[Date]
* fact_Sales[Sales Date] -> dim_Dates[Date]
DATA VALUES
Create a measure under the dim_Dates table as follows:
In summary means:
Forecast +
Sales if:
sales date is less than the forecast cycle
and there is a date in the matrix columns
otherwise calculate YTD sales
Blank when sales date does not fit in the report's data range
MATRIX LAYOUT
* Rows: Forecast Cycle from dim_Forecast_Cycles
* Columns: Year/Month from dim_Dates
* Values: Datapoint measure
Sounds simple, but it took me more than a year to come with a solution.
Since I'm a PBI apprentice, perhaps this is not the best solution, but hope it helps you.
Best regards.
@Artie Were you able to produce this report? I am trying to create something similar and I am having trouble.
Hi guys,
At last, I managed to build the report.
DATA TABLES
* dim_Dates. Date, Year, Year/Month
* dim_Forecast_Cycles. Cycle (Date), Year, Year/Month
* fact_Forecast. Cyce (Date), Forecasted Month (Date), Quantity.
* fact_Sales. Sales Date (Date), Quantity.
If you could group the information by month, the relationships and performance would be much easier.
RELATIONSHIPS
* fact_Forecast[Cycle] -> dim_Forecast_Cycle[Cycle]
* fact_Forecast[Forecasted Month] -> dim_Dates[Date]
* fact_Sales[Sales Date] -> dim_Dates[Date]
DATA VALUES
Create a measure under the dim_Dates table as follows:
In summary means:
Forecast +
Sales if:
sales date is less than the forecast cycle
and there is a date in the matrix columns
otherwise calculate YTD sales
Blank when sales date does not fit in the report's data range
MATRIX LAYOUT
* Rows: Forecast Cycle from dim_Forecast_Cycles
* Columns: Year/Month from dim_Dates
* Values: Datapoint measure
Sounds simple, but it took me more than a year to come with a solution.
Since I'm a PBI apprentice, perhaps this is not the best solution, but hope it helps you.
Best regards.
Could you please confirm if this DAX function and solution can generate the desired report? I'm having issues with the report I produced. May I seek your advice on this matter?
Could this be explained without your specific tables and columns as in the general process?
I have figured how to create the chart - how did you conditionally format the colors to follow your excel table?
This is a standard report in Supply Chain, please advise how to generate this.
* Forecast waterfall
* Commit Waterfall
* Forecast Commit Waterfall
Hi,
Share some data and show the expected result.
hi, @Artie
We could have a try about it, Could you please share your sample pbix file or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |