cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webbm
Frequent Visitor

Return Earliest date when Dax measure value hits 0

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

webbm_0-1656087086548.png


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. 

webbm_1-1656087330212.png

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_0-1656088054008.png

 

1 ACCEPTED SOLUTION
webbm
Frequent Visitor

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

 

 

View solution in original post

3 REPLIES 3
webbm
Frequent Visitor

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
Memorable Member
Memorable Member

@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
Frequent Visitor

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors