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, 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 dufoq3, I can see you may be trying to include a custom function to address the issue, but the values for the sum of "Current Forecast" and "Locked Forecast" are incorrect when I load to a pivot table.
I will focus on BL834R500FT/JLG for the period of March 2026 for example. Referencing the attached photo, the correct value that I would expect would be 0, as there was no forecast data (located by filtering the forecast tab) submitted by the customer for this month. For the locked forecast value, I would expect the value to be 0.12 since this value was forecasted 4 months prior for consumption in March 2026. The values work fine only if order demand actually occurs within the evaluated month.
To verify, I added a dummy order line in the "Deliveries" tab, but I would like to avoid this.
Let me know if this helps.
