How to build a "Waterfall" Forecast Report

[Artie]

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.

[Anonymous]

@Artie Were you able to produce this report? I am trying to create something similar and I am having trouble.

[Artie · Accepted 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:

DataPoint =

SUM(fact_Forecast[Quantity])+

IF(SELECTEDVALUE(dim_Forecast_Cycles[YYMM])>SELECTEDVALUE(dim_Dates[YYMM])&&SELECTEDVALUE(dim_Forecast_Cycles[YYMM])<=FORMAT(EOMONTH(CALCULATE(MAX(dim_Dates[Date]),ALLSELECTED(dim_Dates[YYMM])),0)+1,"YY/MM"),

IF(HASONEVALUE(dim_Dates[YYMM]),SUM(fact_Sales[Quantity]),CALCULATE(SUM(fact_Sales[Quantity]),FILTER(ALLSELECTED(dim_Dates[YYMM]),dim_Dates[YYMM]<SELECTEDVALUE(dim_Forecast_Cycles[YYMM])))),

BLANK())

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.

[hilary_Fang]

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?

[JasonUser000]

Could this be explained without your specific tables and columns as in the general process?

[JasonUser000]

I have figured how to create the chart - how did you conditionally format the colors to follow your excel table?

[Anonymous]

This is a standard report in Supply Chain, please advise how to generate this.

* Forecast waterfall

* Commit Waterfall

* Forecast Commit Waterfall

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-lili6-msft]

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