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

v-hashadapu's avatar
v-hashadapu
Community Support
4 months ago

Hi jrhaney23 , Please try below measures instead of previous measures.

 

Locked Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

 

VAR LockedMonth =
    EDATE ( SelectedMonth, -4 )

 

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    REMOVEFILTERS ( dDate ),
    fForecast[Month] = SelectedMonth,
    fForecast[Forecast Key] = LockedMonth
)

 

 

Current Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

 

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    REMOVEFILTERS ( dDate ),
    fForecast[Month] = SelectedMonth,
    fForecast[Forecast Key] = SelectedMonth
)

 

 

2. Order = SUM ( fOrder[Order Demand] )

 

3. Delta = [Order] - [Locked Forecast]

 

 

4. Please try below relationships.

 

fForecast[Month] --> dDate[Date]
fOrder[Month] --> dDate[Date] 
Customer and Material connected via dimensions

 

Note: No relationship on Forecast Key to Date table

jrhaney23's avatar
jrhaney23
Frequent Visitor
4 months ago

Hi v-hashadapu,

 

When you say Customer and Material connected via dimensions, do you mean the dCustomer and dMaterial tables? Also, I had to afjust the formulas a bit since Excel did not recognize REMOVEFILTERS:

 

Locked Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

 

VAR LockedMonth =
    EDATE ( SelectedMonth, -4 )

 

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    ALL ( dDate ),
    fForecast[Month] = SelectedMonth,
    fForecast[Forecast Key] = LockedMonth
)

 

 

Current Forecast =
VAR SelectedMonth =
    MAX ( dDate[Date] )

 

RETURN
CALCULATE (
    SUM ( fForecast[Total Reel Demand] ),
    ALL ( dDate ),
    fForecast[Month] = SelectedMonth,
    fForecast[Forecast Key] = SelectedMonth
)

 

What are your thoughts? The forumla works fine when I work with smaller data and one Forecast Material.

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    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's avatar
      jrhaney23
      Frequent Visitor
      4 months ago

      Hi v-hashadapu,

       

      This is working as intended. Thanks for your patience and help on this!

       

      • v-hashadapu's avatar
        v-hashadapu
        Community Support
        4 months ago

        Hi jrhaney23 , happy to know it works. Always happy to help. If you have any other queries, please feel free to create new posts here in the community. We are always happy to help.