Forum Discussion

jrhaney23's avatar
jrhaney23
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's avatar
    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
                )
            )
    )

BA_Pete's avatar
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's avatar
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.