Sales Summary - Order Demand vs. Forecast Demand

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.

1. Create a fact + dimensions model, not merged tables:

 Fact Tables (FactForecast, FactOrders)
Dimensions Tables (Material, Customer and Date (Month))

2. 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

3. 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 jrhaney23 ,

I'm not able to download the file due to org restrictions but, if you're able to share small, copyable, examples of each of the tables you're referencing I'm happy to try and help.

The ideal format would be M code for the data based on JSON Binary (by using 'Enter Data' function in Power Query) if possible, as this can then include all of the correct data types etc.

Please remove/anonymise any sensitive data.

Pete

Hi jrhaney23, I've edited your Order vs Forecast query a bit:
Is this what you want?

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}}),
    // Full outer join!
    #"Current Forecast Merge" = Table.NestedJoin(#"Changed Type", {"Current Forecast Key", "Forecast Material", "Customer", "Month"}, Forecast, {"Forecast Key", "Forecast Material", "Customer", "Month"}, "Forecast", JoinKind.FullOuter),
    #"Expanded Forecast" = Table.ExpandTableColumn(#"Current Forecast Merge", "Forecast", {"Forecast Material", "Customer", "Month", "Total Reel Demand"}, {"Forecast Material.1", "Customer.1", "Month.1", "Current Forecast Demand"}),
    #"Locked Forecast Merge" = Table.NestedJoin(#"Expanded Forecast", {"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"}, {"Locked Forecast Demand"}),
    FilledCols = Table.FromRecords(Table.TransformRows(#"Expanded Forecast1", each 
        [ A = List.Select(Record.FieldNames(_), (x)=> Text.Contains(x, ".1")),
          B = _ & Record.Combine(List.Transform(A, (x)=> let B1 = Text.TrimEnd(x, Text.ToList(".1")) in Record.AddField([], B1, if Record.Field(_, B1) = null then Record.Field(_, x) else Record.Field(_, B1))))
        ][B] ), Value.Type(Table.FirstN(#"Expanded Forecast1", 0)) ),
    #"Grouped Rows1" = Table.Group(FilledCols, {"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}}),
    #"∆" = Table.AddColumn(#"Sorted Rows", "∆", each ([Order] ?? 0) - ([Locked Forecast] ?? 0), type number)
in
    #"∆"

hi jrhaney23,

I would suggest to have a bridge table between the two so even if you don't have the a record in the order, you can still see your forecast.

Hope this helps.

Hi jrhaney23 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

Hi jrhaney23 , 

Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.