Forum Discussion
Sales Summary - Order Demand vs. Forecast Demand
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
)
)
)
Hi jrhaney23 , Thank you for reaching out to the Microsoft Community Forum.
Your current approach is order-driven, so when orders don’t exist --> forecast disappears. But your requirement is forecast driven analysis, with orders as optional. Your M query starts from Source = Order. So, everything downstream depends on Order rows existing. If No Order --> no row --> no Forecast --> no Locked Forecast. That’s why JLG disappears unless you add a dummy order. Instead of Power Query, try to use DAX measures.
Please try the below steps to get the desire result.
- Create a fact + dimensions model, not merged tables:
Fact Tables (FactForecast, FactOrders)
Dimensions Tables (Material, Customer and Date (Month))
- Please build the below relationships.
Orders --> Date Many-to-one
Forecast --> Date Many-to-one
Orders --> Customer Many-to-one
Forecast --> Customer Many-to-one
Orders --> Material Many-to-one
Forecast --> Material Many-to-one
- Create Base measures.
Order Demand =
SUM ( Orders[Order Demand] )
Current Forecast =
SUM ( Forecast[Total Reel Demand] )
Locked Forecast =
CALCULATE (
SUM ( Forecast[Total Reel Demand] ),
DATEADD ( 'Date'[Date], -4, MONTH )
)
Note: This measure will shifts the filter context 4 months back.
Forecast table must be connected to a proper Date table, not raw Month columns.
Date Table =
CALENDAR ( DATE(2024,1,1), DATE(2027,12,31) )
Month = DATE(YEAR([Date]), MONTH([Date]), 1)
Hi v-hashadapu,
Getting a bit closer here. Unfortunately, it looks like the values for current and locked forecast are incorrect. Below are what my relationships look like.
Obviously, I would need to create a bridge table to solve the many-to-many relationship with the material.
Perhaps I would need to create another bridge table to filter the correct forecast dates/periods from fForecast?
Let me know your thoughts when possible, or if you need more data/context.
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-...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.
