Return Earliest date when Dax measure value hits 0

[webbm]

Hi All,

I'm trying to return the earliest date where my Stock Forecast reaches Zero. I have had some success with the below code...

OOS Forecast (Trade) = 
CALCULATE (
    MIN ( dimDate[CalendarDate] ),
    FILTER (
        ALL ( dimDate ),
        [YA Stock Forecast (Trade)] < 0
            && dimDate[CalendarDate] > TODAY ()
    )
)

 
This returns the correct date when only one ProductId is in a table

However across a number of ProductId's it returns the earliest date for all not individually as I would like. Notice how 465510 has a different date in the table below. Nothing has change apart from the ProductId filter generating this table. 

My output table is referencing a "dimProduct" Table the relationship is on ProductKey to a dimSales and other tables.

[YA Stock Forecast (Trade)] is a DAX measure built from the code below, essentially [Stock] - [Forecast Sales] ...

YA Stock Forecast (Trade) = CALCULATE (
    'Product Stock'[Stock QTY] - [Consumption Forecast (Trade)]
        + SUM ( 'factProjectedVendorOrder'[PO QTY] ),
    dimDate[CalendarDate]
        > TODAY () - 1,
    FILTER (
        ALLSELECTED ( dimDate[CalendarDate] ),
        dimDate[CalendarDate] <= MAX ( dimDate[CalendarDate] )
    )
)

Due to the volume of data I would like to keep forecast calculations within a measure not create a seperate table. Any help from someone more knowledgeable than myself would be really appreciated! I feel like I'm close but maybe missing something obvious?

** EDIT**
If it helps this is the output for a table generated with dimDate[CalandarDate], Measures[YA Stock Forecast (Trade)] filtered on "ProductId is 465510" around the dates im interested in. 

[webbm · Accepted solution]

For anyone else who comes across this in the future...

I couldn't find a more elegant solution to the problem so I created a calculated table for all ProductId's and moved the measure into a calculated column. This allowed me to add a line filter on the measure to ensure it is only looking at the single ProductId then created a relationship with dimProduct to pull the dates through into the origonal table.

Product Stock Dates (Table) = 
SUMMARIZE (
    dimCyclingProducts,
    dimCyclingProducts[ProductId],
    dimCyclingProducts[ProductKey]
)

Out of Stock Date (Column) =
CALCULATE (
    CALCULATE (
        MIN ( dimDate[CalendarDate] ),
        FILTER (
            ALL ( dimDate ),
            [YA Stock Forecast (Trade)] < 0
                && dimDate[CalendarDate] > TODAY ()
        )
    ),
    FILTER (
        ALL ( dimCyclingProducts ),
        dimCyclingProducts[ProductKey] = 'Product OOS Dates'[ProductKey]
    )
)

[ribisht17]

@webbm 

The reason is probably  making use of ALL there that will force the filter context to Table level , you need to avoid that I guess

OOS Forecast (Trade) = 
CALCULATE (
    MIN ( dimDate[CalendarDate] ),
    FILTER (
        ALL ( dimDate ),
        [YA Stock Forecast (Trade)] < 0
            && dimDate[CalendarDate] > TODAY ()
    )
)

Regards,

Ritesh

[webbm]

@ribisht17 Thanks for the response. I've removed All and it performing the same as before unfortunately.

OOS Forecast (Trade) 2 = 
CALCULATE (
    MIN ( dimDate[CalendarDate] ),
    FILTER (
        dimDate,
        [YA Stock Forecast (Trade)] < 0
            && dimDate[CalendarDate] > TODAY ()
    )
)