Forum Discussion

Frequent Visitor

4 months ago

Solved

Sales Summary - Order Demand vs. Forecast Demand

Hello, I am trying to develop a better relationship with order data vs. forecast data for a range of forecasted materials. In the shared workbook, you will find a "Forecast" and an "Order vs. Forecas...

Need Help

Tips and Tricks

v-hashadapu
4 months ago
Hi jrhaney23 , yes, Use your dimension tables dCustomer, dMaterial and connect them:

fOrder[Customer] --> dCustomer[Customer]
fForecast[Customer] --> dCustomer[Customer]
fOrder[Forecast Material] --> dMaterial[Forecast Material]
fForecast[Forecast Material] --> dMaterial[Forecast Material]

Use "ALL(dDate)" instead of "REMOVEFILTERS"

Use FILTER instead of direct column filters.

Please try below updated DAX measures.

Locked Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

VAR LockedMonth =
    EDATE ( SelectedMonth, -4 )

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    ALL ( dDate ),
    FILTER (
        fForecast,
        fForecast[Month] = SelectedMonth
&& fForecast[Forecast Key] = LockedMonth
    )
)

Current Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    ALL ( dDate ),
    FILTER (
        fForecast,
        fForecast[Month] = SelectedMonth
&& fForecast[Forecast Key] = SelectedMonth
    )
)

If you are facing any grand total inconsistencies, Please try below DAX.

Locked Forecast =
SUMX (
    VALUES ( dDate[Date] ),
    VAR SelectedMonth = dDate[Date]
    VAR LockedMonth = EDATE ( SelectedMonth, -4 )
    RETURN
        CALCULATE (
            SUM ( fForecast[Total Reel Demand] ),
            ALL ( dDate ),
            FILTER (
                fForecast,
                fForecast[Month] = SelectedMonth
&& fForecast[Forecast Key] = LockedMonth
            )
        )
)

v-hashadapu

Community Support

4 months ago

Hi jrhaney23 , To understand and answer your issue completely, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot or a .pbix). Include all the necessary details, detailing your scenario and issue as clearly and fully as possible.

Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

jrhaney23

Frequent Visitor

4 months ago

Hi v-hashadapu,

Below you will find the tables I'm working with:

fOrder

Customer	Sales Order	Line	Material	Customer Material	Quantity	UoM	Forecast Material	Month	Order Demand
JLG	325784	10	BL834X177B	91513035S	3	EA	BL834R500FT	10/1/2025	0.09
JLG	326078	20	BL834X177B	91513035S	26	EA	BL834R500FT	11/1/2025	0.77
JLG	325431	10	BL834X177B	91513035S	13	EA	BL834R500FT	11/1/2025	0.38
JLG	332868	20	BL834X177B	91513035S	2	EA	BL834R500FT	4/1/2026	0.06

fForecast

Forecast Key	Forecast Material	Customer	Month	Total Reel Demand
11/1/2025	BL834R500FT	JLG	3/1/2026	0.12
11/1/2025	BL834R500FT	JLG	4/1/2026	0.3
11/1/2025	BL834R500FT	JLG	5/1/2026	0.12
3/1/2026	BL834R500FT	JLG	5/1/2026	0.09

dMaterial

Material	Description	Customer Material	Customer	UoM	Links	Pitch	Material Length	Forecast Material	Forecast Material Length	Reel Demand
500ABC208	BL834XL X 69 LKS ILEE	051060-011	Crown	EA	69	1	5.75	BL834R500FT	500	0.01
BL834N97B	BL834 X 97 LKS ILEE	051060-028	Crown	EA	97	1	8.08	BL834R500FT	500	0.02
BL844N119B	BL844 X 119 LKS ILEE	089260-144	Crown	EA	119	1	9.92	BL834R500FT	500	0.02
500AAE781R250FT	BL834XL LEAFCHAIN TX8R LUBE 250FT REEL	1147352	Raymond	FT	3000	1	250	BL834R500FT	500	0.5
500AAE781R100FT	BL834XL LEAFCHAIN TX8R LUBE 100FT REEL	1147352/100	Raymond	FT	1200	1	100	BL834R500FT	500	0.2
BL834R250FT	BL834 XL X 250 FT REEL	249808	Hyster-Yale	FT	3000	1	250	BL834R500FT	500	0.5
BL834R100FT	BL834 X 100FT	249808	Hyster-Yale	FT	1200	1	100	BL834R500FT	500	0.2
BL834X177B	BL834 X 177 LKS ILE (BOXED)	91513035S	JLG	EA	177	1	14.75	BL834R500FT	500	0.03
BL834R100FT	BL834 X 100FT	340834-100	Crown	FT	1200	1	100	BL834R500FT	500	0.2
BL834R100FT	BL834 X 100FT	4077667	Hyster-Yale	FT	1200	1	100	BL834R500FT	500	0.2

dCustomer

Customer

Crown

Raymond

TMH

NEW

JLG

Hyster-Yale

Vermeer

dDate

Date

1/1/2025

2/1/2025

3/1/2025

4/1/2025

5/1/2025

6/1/2025

7/1/2025

8/1/2025

9/1/2025

10/1/2025

11/1/2025

12/1/2025

1/1/2026

2/1/2026

3/1/2026

4/1/2026

5/1/2026

6/1/2026

7/1/2026

8/1/2026

9/1/2026

10/1/2026

11/1/2026

12/1/2026

1/1/2027

2/1/2027

3/1/2027

4/1/2027

5/1/2027

6/1/2027

7/1/2027

8/1/2027

9/1/2027

10/1/2027

11/1/2027

12/1/2027

Problem: By using the merge feature when referencing the correct forecast "keys" for a paticluar month, if there is no order demand there will also be no forecast demand listed.

Having a "0" or null value in the order demand shown with the correct current and locked forecasts will be important to determine variances in sales. This will also help our team manage expected inventory across multiple plant locations.

Problem Visuial in Pivot Table:

As you can see, there is forecast data available in fForecast for the month of March from the locked forecast key 4 months prior.

The ideal scenario would be to include all forecast values whether there is order demand or not.

So far, I have tried moving this into a data model to solve by using DAX calculations. I am currently looking at solving by using a combination of CALCULATE, SUM, Filter, Filter All functions, but no luck yet.

Here is a dropbox location for the file: FLT Sales Summary 2026 NEW - Data-Model

The original file is in the orginal question.

Let me know if you need anything else.

v-hashadapu
Community Support
4 months ago
Hi jrhaney23 , Please try below measures instead of previous measures.

Locked Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

VAR LockedMonth =
    EDATE ( SelectedMonth, -4 )

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    REMOVEFILTERS ( dDate ),
    fForecast[Month] = SelectedMonth,
    fForecast[Forecast Key] = LockedMonth
)

Current Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    REMOVEFILTERS ( dDate ),
    fForecast[Month] = SelectedMonth,
    fForecast[Forecast Key] = SelectedMonth
)

2. Order = SUM ( fOrder[Order Demand] )

3. Delta = [Order] - [Locked Forecast]

4. Please try below relationships.

fForecast[Month] --> dDate[Date]
fOrder[Month] --> dDate[Date]
Customer and Material connected via dimensions

Note: No relationship on Forecast Key to Date table
- jrhaney23
  Frequent Visitor
  4 months ago
  Hi v-hashadapu,
  
  When you say Customer and Material connected via dimensions, do you mean the dCustomer and dMaterial tables? Also, I had to afjust the formulas a bit since Excel did not recognize REMOVEFILTERS:
  
  Locked Forecast =
  VAR SelectedMonth =
      MAX ( dDate[Date] )
  
  VAR LockedMonth =
      EDATE ( SelectedMonth, -4 )
  
  RETURN
  CALCULATE (
      SUM ( fForecast[Total Reel Demand] ),
  ALL ( dDate ),
      fForecast[Month] = SelectedMonth,
      fForecast[Forecast Key] = LockedMonth
  )
  
  Current Forecast =
  VAR SelectedMonth =
      MAX ( dDate[Date] )
  
  RETURN
  CALCULATE (
      SUM ( fForecast[Total Reel Demand] ),
      ALL ( dDate ),
      fForecast[Month] = SelectedMonth,
      fForecast[Forecast Key] = SelectedMonth
  )
  
  What are your thoughts? The forumla works fine when I work with smaller data and one Forecast Material.
  - v-hashadapu
    Community Support
    4 months ago
    Hi jrhaney23 , yes, Use your dimension tables dCustomer, dMaterial and connect them:
    
    fOrder[Customer] --> dCustomer[Customer]
    fForecast[Customer] --> dCustomer[Customer]
    fOrder[Forecast Material] --> dMaterial[Forecast Material]
    fForecast[Forecast Material] --> dMaterial[Forecast Material]
    
    Use "ALL(dDate)" instead of "REMOVEFILTERS"
    
    Use FILTER instead of direct column filters.
    
    Please try below updated DAX measures.
    
    Locked Forecast =
    VAR SelectedMonth =
        MAX ( dDate[Date] )
    
    VAR LockedMonth =
        EDATE ( SelectedMonth, -4 )
    
    RETURN
    CALCULATE (
        SUM ( fForecast[Total Reel Demand] ),
        ALL ( dDate ),
        FILTER (
            fForecast,
            fForecast[Month] = SelectedMonth
    && fForecast[Forecast Key] = LockedMonth
        )
    )
    
    Current Forecast =
    VAR SelectedMonth =
        MAX ( dDate[Date] )
    
    RETURN
    CALCULATE (
        SUM ( fForecast[Total Reel Demand] ),
        ALL ( dDate ),
        FILTER (
            fForecast,
            fForecast[Month] = SelectedMonth
    && fForecast[Forecast Key] = SelectedMonth
        )
    )
    
    If you are facing any grand total inconsistencies, Please try below DAX.
    
    Locked Forecast =
    SUMX (
        VALUES ( dDate[Date] ),
        VAR SelectedMonth = dDate[Date]
        VAR LockedMonth = EDATE ( SelectedMonth, -4 )
        RETURN
            CALCULATE (
                SUM ( fForecast[Total Reel Demand] ),
                ALL ( dDate ),
                FILTER (
                    fForecast,
                    fForecast[Month] = SelectedMonth
    && fForecast[Forecast Key] = LockedMonth
                )
            )
    )

Forum Discussion

Sales Summary - Order Demand vs. Forecast Demand

Related content

Demand Forecast formula help

Power BI July 2026 Feature Summary

Forecasting with DAX

Forecasting

Live Weather Forecast Dashboard