Cut-off Date from Different Table

[RobRayborn]

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 forecasted amount was before orders started being taken.

I have a Forecast Table, a Orders Taken Table and a Date Table with offsets.

The Forecast Table has columns for Product, Created Date, Quantity, and Ship Date.
The Orders Taken table has columns for the Product, DATE CREATED, Quantity, and Ship Date.
The Date column of the Date table is linked to the Ship Date of the Forecast Table and the Ship Date of the Orders Taken Table.
There is a common Product table that holds unique values from both the Orders Taken Product column and Forecast Table Product column.
I need a DAX measure that will change the Quantity in the Forecast Table going forward to BLANK() on the first date when the Order Quantity of the Orders Taken Date Created is greater than zero.

[v-csrikanth]

Hi @RobRayborn 
Just a reminder did you got any chance to try the suggested solution.

If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @RobRayborn 
Thanks for your quick response.
Please let us know if you face any issues while reproing.

Best Regards,
Cheri Srikanth

[v-csrikanth]

Hi @RobRayborn 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @RobRayborn 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[RobRayborn]

My appologies. I haven't had time to try the solution out yet.
Hopefully by the end of the week this week.

[v-csrikanth]

Hi @RobRayborn 
Sorry for the late response.
You can blank out forecast quantities once orders start by first capturing, for each product, the date when the first order was created, and then using that to blank your forecast measure on or after that date. For example:

Adjusted Forecast Quantity =
VAR CurrentProduct = SELECTEDVALUE( Product[Product] )
VAR CurrentShipDate = SELECTEDVALUE( Date[Date] )
VAR FirstOrderDateForProduct =
CALCULATE(
MIN( 'Orders Taken'[DATE CREATED] ),
FILTER(
ALL( 'Orders Taken' ),
'Orders Taken'[Product] = CurrentProduct
)
)
RETURN
IF(
NOT ISBLANK( FirstOrderDateForProduct )
&& CurrentShipDate >= FirstOrderDateForProduct,
BLANK(),
SUM( Forecast[Quantity] )
)

Place this measure in your matrix alongside the Date and Product contexts. It will show the original forecast amount only for ship dates before the first order for that product, and return blank thereafter.

For more on these functionscheck the reference links as below: 
See SELECTEDVALUE (https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax) 
CALCULATE (https://learn.microsoft.com/en-us/dax/calculate-function-dax).

If this helps, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[RobRayborn]

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.

[rajendraongole1 · Accepted solution]

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.