Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
1 year ago
Solved

Cut-off Date from Different Table

The problem is I have a Forecasting amount that reduces becuase the Orders placed are sub tracked from the monthly forecast amount upon the entry of the next forecast. I need to know what the forecas...
  • rajendraongole1's avatar
    1 year ago

    Hi RobRayborn  - Create a calculated column or measure to identify the first order date per product.

     

    eg: 

    FirstOrderDate =
    CALCULATE(
    MIN('Orders Taken'[DATE CREATED]),
    ALLEXCEPT('Orders Taken', 'Orders Taken'[Product])
    )

     

    Now write the Forecast Measure that blanks quantities after the first order

     

    Adjusted Forecast Quantity =
    VAR CurrentProduct = SELECTEDVALUE('Product'[Product])
    VAR CurrentShipDate = SELECTEDVALUE('Date'[Date])

    VAR FirstOrderDateForProduct =
    CALCULATE(
    MIN('Orders Taken'[DATE CREATED]),
    FILTER('Orders Taken', 'Orders Taken'[Product] = CurrentProduct)
    )

    RETURN
    IF(
    NOT ISBLANK(FirstOrderDateForProduct) && CurrentShipDate >= FirstOrderDateForProduct,
    BLANK(),
    SUM('Forecast'[Quantity])
    )

     

    Place this Adjusted Forecast Quantity measure on visuals using the Date, Product, and Forecast context.

    It will show the original forecast only before any order activity started per product. Hope this works.