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
            )
        )
)

jrhaney23

Frequent Visitor

4 months ago

Hi BA_Pete,

I'll try my best to share some additional details here. Below you will find the three tables I'm working with.

Table 1 = Order

Table 2 = Forecast

Table 3 = Order vs Forecast

The goal is to analyze order demand vs. forecast date from a given period (current vs. locked forecast data (4 months prior)).

These photos highlight that since customer JLG has no order data, there is no forecast data populated. BUT there is locked forecast data 4 months prior (forecast key 11/1/2025) from which we had planned to produce. I would like to keep that data in my analysis.

Below is the M Code for the Order vs Forecast Query:

let
    Source = Order,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Forecast Material", "Customer", "Order Demand", "Month"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Forecast Material", "Customer", "Month"}, {{"Order Demand", each List.Sum([Order Demand]), type nullable number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Forecast Material", "Customer", "Order Demand", "Month"}),
    #"Current Forecast Key" = Table.AddColumn(#"Reordered Columns", "Current Forecast Key", each [Month]),
    #"Locked Forecast Key" = Table.AddColumn(#"Current Forecast Key", "Locked Forecast Key", each Date.AddMonths([Month], -4)),
    #"Changed Type" = Table.TransformColumnTypes(#"Locked Forecast Key",{{"Current Forecast Key", type date}, {"Locked Forecast Key", type date}}),
    #"Current Forecast Merge" = Table.NestedJoin(#"Changed Type", {"Current Forecast Key", "Forecast Material", "Customer", "Month"}, Forecast, {"Forecast Key", "Forecast Material", "Customer", "Month"}, "Forecast", JoinKind.LeftOuter),
    #"Expanded Forecast" = Table.ExpandTableColumn(#"Current Forecast Merge", "Forecast", {"Total Reel Demand"}, {"Total Reel Demand"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Forecast",{{"Total Reel Demand", "Current Forecast Demand"}}),
    #"Locked Forecast Merge" = Table.NestedJoin(#"Renamed Columns1", {"Locked Forecast Key", "Forecast Material", "Customer", "Month"}, Forecast, {"Forecast Key", "Forecast Material", "Customer", "Month"}, "Forecast", JoinKind.LeftOuter),
    #"Expanded Forecast1" = Table.ExpandTableColumn(#"Locked Forecast Merge", "Forecast", {"Total Reel Demand"}, {"Total Reel Demand"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Forecast1",{{"Total Reel Demand", "Locked Forecast Demand"}}),
    #"Grouped Rows1" = Table.Group(#"Renamed Columns2", {"Forecast Material", "Customer", "Month"}, {{"Order", each List.Sum([Order Demand]), type nullable number}, {"Current Forecast", each List.Sum([Current Forecast Demand]), type nullable number}, {"Locked Forecast", each List.Sum([Locked Forecast Demand]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"Month", Order.Descending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"Current Forecast", "Locked Forecast"}),
    #"∆" = Table.AddColumn(#"Replaced Value", "∆", each [Order]-[Locked Forecast], type number)
in
    #"∆"

I'm thinking of pivoting from trying to solve this via M Code to DAX since this is evolving more into a calculation rather than ETL. I have tried a bridge table, but without the relevant data from 'Order', this would most likely result in the same situation.

Let me know your thoughs when possible, or if you need more information.

BA_Pete

Super User

4 months ago

Hi jrhaney23 ,

The M code you've pasted still doesn't have any copyable data in it. This references a table I don't have, so can't recreate your scenario my side to start trying to help:

Use the Enter Data function in Power Query to just copy the tables you've made screenshots of (top left of table in PQ > 'Copy enttire table') and paste them in there then, when you 'OK' that pasted table, you'll see that there's M code for that table based on a JSON Binary. Copy that M code and paste here and that will give me a ready-made table in PQ that's exactly the same as what you're seeing in your screenshots.

To note: It's after 6pm here now so I'm unlikely to pick this up over the weekend to be honest.

Pete

jrhaney23

Frequent Visitor

4 months ago

Hi BA_Pete,

Thanks for clarifying, I actually had no idea you could do this. Let me know if this is what you are looking for.

'Order'

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZJNa8JAEIb/S866mY+d/TjGYumh9BC8FPEg6M0q2IL4792PaJTYlgTBy2SzO/POs+/OfF4wead1MSoQQpi8O9Y1ArzOwh9p78CFhUA8THtccklA5pIsTXIKb8fvn/V+/LncrJMkXIpzxKSAWWExCu0ZjDfn9iGpqqbWYU2SVRG1ZaEIE7ZaCPMHRL08fu22q1gjbeumXivpMpBrGZLgh7eTsAZBMDAGiiZEk6ZVLrb326fjl/3usA1fp668Y1IcbwEKTLc/Yrrivx7gzUOg621CI8AdBAoC0kU4T0KDUOYXpQEU17OQ60HpO04I2v5OkDzOCUbP2H8gGR87kcwDJmIAxO8+PBdhcQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order" = _t, Line = _t, Material = _t, #"Customer Material" = _t, Quantity = _t, UoM = _t, #"GI Date" = _t, #"Forecast Material" = _t, UoM.1 = _t, Customer = _t, #"Material Length" = _t, #"Forecast Material Length" = _t, #"Length Total" = _t, #"Order Demand" = _t, Month = _t])
in
    Source

'Forecast'

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUN9Q3MjAyVdJRcvKxMDYJMjUwcAsB8rx83IGkMUTaDMg00DM0UorVwacnKLEyNz8vBVWfpR4hbR6VxSWpRbqRiTmpqFoNwRqRBIiwzlzPUik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Forecast Key" = _t, #"Forecast Material" = _t, Customer = _t, Month = _t, #"Total Reel Demand" = _t])
in
    Source

'Order vs Forecast'

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvKxMDYJMjUwcAtR0lFyLsovzwPSxvqG+kYGRmZApoGeAZiCYRA3Vgddo0dlcUlqkW5kYk4qqnZDqD4IjakxKLEyNz8vBVWTkaGeCZAy17MEkpZ6RiD9RkAqNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Forecast Material" = _t, Customer = _t, Month = _t, Order = _t, #"Current Forecast" = _t, #"Locked Forecast" = _t, #"∆" = _t])
in
    Source

P.S.,

Ok to review at your convenience. This is not an emergency; rather, I'm looking for a way to improve a very manual process.

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