Forum Discussion
Cut-off Date from Different Table
- 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.
Nope. Sorry didn't work. In the example below the Date Table is attached to the Forecast ship date (Columns). The dates for the rows are the Created Dates from the Forecast Table and are not linked to the Date Table. Date Created is part of the Orders Table.
As I'm pointing out in the picture as examples. On 2/10/2025 the first order was placed for Mar 2025, that means all Forecast data > that date should become blank (1,440), (becasue the customer is placing orders towards that amount). Just as on 3/3/2025 the customer placed an order to ship in Apr 2025, therefore all Forecast data for the Apr 2025 that came in >3/3/2025 should become blank (1,280). Only the later forecasted amounts should become blank, keeping the previous Forecast Date Created and amounts.